You Don’t need SysDBA privilege to run Awrrpt..

Well I tried to Sensationalize this post by using such a heading 🙂 Though I just wanted to point out that Awrrpt script can be run without sysdba privilege too. Actually if you open awrrpti.sql script , it contains following line

Rem    NOTES
Rem      Run as SYSDBA.  Generally this script should be invoked by awrrpt,
Rem      unless you want to pick a database other than the default.

In actual you need only two privileges to run the script. One is SELECT_CATALOG_ROLE and other is execute permission on dbms_workload_repository procedure. If you don’t give privilege explicitly on this package you get following errors

select output from table(dbms_workload_repository.awr_report_text( :dbid,
                         *
ERROR at line 1:
ORA-00904: : invalid identifier

Therefore following command’s does the trick.

grant select_catalog_role to amit;
grant execute on dbms_workload_repository to amit;

Tags: 10goraclePerformance