Sometimes during a project there are people that would like to generate AWR reports. Here are the privilges on certain database objecs that a user needs to be able to generate such reports using SQL*Plus (in this case granted to a role).
CREATE ROLE awr_user; GRANT SELECT ON SYS.V_$DATABASE TO awr_user; GRANT SELECT ON SYS.V_$INSTANCE TO awr_user; GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO awr_user; GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO awr_user; GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO awr_user;
In short for generating an AWR report from within SQL*Plus
Getting the snapshot id’s for available snapshots taken (starting yesterday) and other information needed for generating the AWR report:
SELECT snap_id, dbid, instance_number, end_interval_time FROM dba_hist_snapshot WHERE end_interval_time > trunc(sysdate-1) ORDER BY snap_id;
To generate an AWR report (in HTML format) use the following statement:
Don’t forget to start spooling before using the command. There will be a lot of output generated!
SELECT * FROM TABLE(sys.DBMS_WORKLOAD_REPOSITORY.awr_report_html(<dbid>,<instance_number>,<snapshot_id begin>,<snapshot_id eind>));