Oracle SQL Developer - Autotrace - Insufficient Privileges

Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database in both traditional and Cloud deployments. Oracle SQL Developer is my favorite IDE even over Toad and PL/SQL Developer for following main reasons:
1. Oracle SQL Developer provides all features which any developer requires
2. Oracle SQL Developer has DBA console for managing and monitoring database
3. And Yes, Oracle SQL Developer is free :)

Now coming to the topic -

AUTOTRACE in Oracle SQL Developer comes with awesome features like Hotspot and Compare Different Plans and statistics. We need PLUSTRACE role to generate AUTOTRACE in SQLPLUS, but what if we want to generate AUTOTRACE in Oracle SQL Developer. Even if your schema has PLUSTRACE role, Oracle SQL Developer does not execute AUTOTRACE and command fails with "Insufficient Privileges" as below -

Reference: Query Tuning 101 How to Run Autotrace in SQL Developer

So as we can see in above dialog box that Oracle SQL Developer needs following grants for AUTOTRACE
- SELECT_CATALOG_ROLE
- SELECT ANY DICTIONARY

If you request your DBA for above privileges, they may deny it as these are very powerful privileges.

Surprisingly, Oracle SQL Developer even does not need above privileges for generating AUTOTRACE. Oracle SQL Developer only needs some privileges to generate AUTOTRACE. You can request your DBA to grant following privileges for generating AUTOTRACE in Oracle SQL Developer

create role osd_plustrace;

grant select on v_$sesstat to osd_plustrace;
grant select on v_$statname to osd_plustrace;
grant select on v_$mystat to osd_plustrace;
grant select on v_$session to osd_plustrace;
grant select on v_$sql_plan to osd_plustrace;
grant select on v_$sql_plan_statistics to osd_plustrace;
grant select on v_$sql to osd_plustrace;
grant select on v_$sql_plan_statistics_all to osd_plustrace;

grant osd_plustrace to dba with admin option;

grant osd_plustrace role to nimish;

Here, I have created "osd_plustrace" ROLE (where osd is Oracle SQL Developer) and have granted all the required privileges to "osd_plustrace" role. DBA can grant "osd_plustrace" ROLE later to any SCHEMA which needs to generate AUTOTRACE report from Oracle SQL Developer.

Happy Auto-tracing and Tuning :)

Related Posts:
- How to Get Execution Plan and Statistics of SQL Query
- How to Enable Plustrace Role to Generate AUTOTRACE
- DBMS_PROFILER: How to analyze PL/SQL performance
- Oracle : TKPROF simple steps
- Alter System Set Events to Generate Trace Files on Ora Error