Misleading ORA-13773 Error for DBMS_SQLTUNE.SELECT_CURSOR_CACHE

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 privilege1

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> 

1 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.

Advertisements

3 thoughts on “Misleading ORA-13773 Error for DBMS_SQLTUNE.SELECT_CURSOR_CACHE

  1. Thank you for the posting. I encountered the same issue on a 10gR2 RAC database attempting to run SQL Tuning Advisor on a specific sql_id. I issued the grant statement you list above (i.e. v_$sqlarea_plan_hash) to the user running the SQL Tuning Advisor and that resolved the problem. Note, I’d already issued “grant advisor to ” prior to this. -Ralph Cavalier: http://sprocketdba.wordpress.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s