DBMS_SQLTUNE | ORAganism

I have just stumbled into this and thought it worth sharing…

Whilst trying to establish the minimum privilege set required to use “SQL Tuning Sets” I hit some misleading Oracle error messages (see below):

Calls to DBMS_SQLTUNE.SELECT_CURSOR_CACHE resulted in errors…

23:45:21 MARTIN@ora11gr2> SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SOE''', NULL, 'ELAPSED_TIME', NULL, NULL, 1, 10, 'ALL')) P;
SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SOE''', NULL, 'ELAPSED_TIME', NULL, NULL, 1, 10, 'ALL')) P
                           *
ERROR at line 1:
ORA-13761: invalid filter
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5543
ORA-06512: at line 1


23:46:27 MARTIN@ora11gr2> SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;
SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P
                           *
ERROR at line 1:
ORA-13773: insufficient privileges to select data from the cursor cache
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5540
ORA-06512: at line 1


23:46:37 MARTIN@ora11gr2>

Looking up the error code provided further detail…

ORA-13773: insufficient privileges to select data from the cursor cache
Cause: The user attempted to perform an operation without having the appropriate privileges on V$SQL and V$SQL_BIND_CAPTURE.
Action: Adjust the user”s privileges and retry the operation.

So, I granted SELECT on V$SQL_BIND_CAPTURE and retested (access to V$SQL was already permitted for user MARTIN), but this didn’t fix the problem and I got the same errors. I then checked My Oracle Support to see if there were any notes on the subject, but that drew a blank.

After digging into the code (via a bit of “unwrapping”) I found that permission to access V$SQLAREA_PLAN_HASH was causing the issue. Granting SELECT on this view resolved the error as shown in the next 2 code sections.

Granting the additional privilege¹ …

23:45:39 SYS@ora11gr2> grant select on v_$sqlarea_plan_hash to martin;

Grant succeeded.

23:45:46 SYS@ora11gr2>

Re-running the queries that had previously failed…

23:46:37 MARTIN@ora11gr2> SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P;

  COUNT(*)
----------
      1345

23:47:01 MARTIN@ora11gr2> SELECT count(*) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SOE''', NULL, 'ELAPSED_TIME', NULL, NULL, 1, 10, 'ALL')) P;

  COUNT(*)
----------
        10

23:47:08 MARTIN@ora11gr2>

¹ Please note that user MARTIN had already been granted SELECT on a range of V$ views and been given both ADVISOR and ADMINISTER SQL TUNING SET system privileges.