Grants to retrieve SQL_MONITOR

Posted on by

A quick post as I have been struggling to get the full list of object level grants to be able to retrieve the output of DBMS_SQLTUNE.REPORT_SQL_MONITOR.

1 – How to investigate the annoying ORA-00942: table or view does not exist error ?

Set the event 942 at the instance or PDB level :

SQL> alter system set events '942 trace name errorstack level 3';

When you get the error, open the tracefile and you’ll get the problematic SQL.

2 – So here is the list of GRANTS needed to call with a wee testcase : SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>’dhpn35zupm8ck’, type=>’EM’, report_level => ‘ALL’) as report FROM dual;

PS: Tested on 12.1.0.2 on linux.


create user test_sqlmon identified by test_sqlmon;

-- Minimum 
grant create session                          TO test_sqlmon;

-- Extra grants
GRANT SELECT ON GV_$SQL                       TO test_sqlmon;
GRANT SELECT ON GV_$SQL_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON GV_$SYS_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON V_$SYS_OPTIMIZER_ENV         TO test_sqlmon;
GRANT SELECT ON GV_$SQL_MONITOR               TO test_sqlmon;
GRANT SELECT ON V_$SQL_MONITOR               TO test_sqlmon;
GRANT SELECT ON GV_$SQL_MONITOR_STATNAME      TO test_sqlmon;
GRANT SELECT ON V_$SQL_MONITOR_STATNAME       TO test_sqlmon;
GRANT SELECT ON GV_$SQL_PLAN_MONITOR          TO test_sqlmon;
GRANT SELECT ON V_$SQL_PLAN_MONITOR            TO test_sqlmon;
GRANT SELECT ON GV_$SESSION_LONGOPS           TO test_sqlmon;
GRANT SELECT ON GV_$INSTANCE                  TO test_sqlmon;
GRANT SELECT ON V_$INSTANCE                  TO test_sqlmon;
GRANT SELECT ON GV_$DATABASE                  TO test_sqlmon;
GRANT SELECT ON V_$DATABASE                  TO test_sqlmon;
GRANT SELECT ON GV_$ACTIVE_SESSION_HISTORY    TO test_sqlmon;
GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY    TO test_sqlmon;
GRANT SELECT ON GV_$TIMER                     TO test_sqlmon;
GRANT SELECT ON V_$TIMER                     TO test_sqlmon;
GRANT SELECT ON GV_$SQL_PLAN                  TO test_sqlmon;
GRANT SELECT ON V_$SQL_PLAN                   TO test_sqlmon;
GRANT SELECT ON GV_$ASH_INFO                  TO test_sqlmon;
GRANT SELECT ON DBA_PROCEDURES                TO test_sqlmon;
GRANT SELECT ON GV_$SQLAREA_PLAN_HASH		      TO test_sqlmon;
GRANT SELECT ON V_$SESSION                    TO test_sqlmon;
GRANT SELECT ON V_$PARAMETER                    TO test_sqlmon;
GRANT SELECT ON V_$PROCESS                    TO test_sqlmon;

==> Connect with the "test_sqlmon" user, try => you'll get something to save to a file or inside a table for further analysis !

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'dhpn35zupm8ck', type=>'EM', report_level => 'ALL') as report  FROM dual;

****/***

-- cleanup
DROP user test_sqlmon cascade;

Hope this helps.

3 – Disable the event

SQL> alter system set events '942 trace name errorstack off';

Leave a comment