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;