Privileges for AWR reporting

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>));
This entry was posted in Database and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*