AWR from Oracle SQLDeveloper, super simple

 

This is going to be a simple post, and a little regurgitation.

You cannot run the classic awrrpt.sql from sqldeveloper, because it cannot access the database servers directories to find the rdbms scripts.  But, it's easy to work around this.

The server actually just calls a procedure, which is listed below (dbms_workload_repository.awr_report_html).  In this example, I want html output.

Therefore, if you are using sqlDeveloper, and you want to run an AWR, it's really easy.

First, get the AWR ID of the start and finish times, using this SQL:

select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot;

Easy, now run the following and press the "Run Statement" green arrow ->

set linesize 8000
set feedback off;
set heading off;
set verify off;
SELECT output FROM    TABLE(dbms_workload_repository.awr_report_html ((select dbid from v$database),(select instance_number from v$instance),1956,1957,8 ));
quit;

Then copy the output from the query result window into a new file and call it AWR.html

Open that in a browser and you are in AWR territory.  Note that you need to be EE and you also need the right permissions.  Enjoy not having access to the database server.