All commands are running under SYS user, but you can use another one with appropriate privileges.
First, let’s compile simple report generation function.
create or replace function AWR_HTML(in_begin_point in number, in_end_point in number) return clob is ret_html clob; db_id number; begin select dbid into db_id from v$database; for i in ( select output from table(dbms_workload_repository.awr_report_html(db_id, 1, in_begin_point, in_end_point)) ) loop ret_html:=ret_html||i.output; end loop; return(ret_html); end AWR_HTML;
After that, we should decide which snapshots to use for report generation.
select snap_id, end_interval_time from dba_hist_snapshot order by snap_id desc
1458 20.01.16 11:00:07,855 1457 20.01.16 10:00:05,418 1456 20.01.16 09:00:02,709 ...
Oracle creates snapshots periodically, as a rule. Run this, if you want to force snapshot creation.
begin dbms_workload_repository.create_snapshot; end;
Everything is ready. Now you can generate report using appropriate snapshot IDs.
select awr_html(1457, 1458) from dual
In case you are using PL/SQL Developer IDE, generated report will be opened using built-in html-viewer. Very smart 🙂