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;

Amit Bansal

Experienced professional with 16 years of expertise in database technologies. In-depth knowledge of designing and implementation of Disaster Recovery / HA solutions, Database Migrations , performance tuning and creating technical solutions. Skills: Oracle,MySQL, PostgreSQL, Aurora, AWS, Redshift, Hadoop (Cloudera) , Elasticsearch, Python

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.