ORA-13761: invalid filter

The Problem

You are trying to populate a SQL Tuning Set (STS) from the cursor cache using the DBMS_SQLTUNE Oracle supplied package, as below:

user@DB> EXEC DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET( –
>                          sqlset_name     => ’10g_TEST’, –
>                          time_limit      => 60, –
>                          repeat_interval => 10, –
>                          capture_option  => ‘MERGE’, –
>                          basic_filter    => ‘PARSING_SCHEMA_NAME = ”TEST”’, –
>                          capture_mode    => dbms_sqltune.MODE_REPLACE_OLD_STATS);
*
ERROR at line 1:
ORA-13761: invalid filter
ORA-06512: at “SYS.DBMS_SQLTUNE”, line 3366
ORA-06512: at line 1

I wasn’t sure if this was because I had an invalid filter or not. I suspected that it wasn’t because of incorrect syntax because I had taken this from an example in

the Oracle documentation. So, I removed the basic_filter and tried it again. This time the error was different:

ERROR at line 1:
ORA-13773: insufficient privileges to select data from the cursor cache
ORA-06512: at “SYS.DBMS_SQLTUNE”, line 3366
ORA-06512: at line 1

The Cause

As clearly stated above I didn’t have sufficient privileges to execute this command.

The Solution

The required privileges are:

GRANT SELECT ON V_$SQL TO TEST;
GRANT SELECT ON V_$SQLAREA TO TEST;
GRANT SELECT ON V_$SQLAREALAN_HASH TO TEST;
GRANT SELECT ON V_$SQLSTATS TO TEST;

If you are receiving the error ORA-38171: Insufficient privileges for SQL management object operation this is covered in another of my articles.

Like it, share it...

Category: SQL