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
Comments (1)
Trackback URL | Comments RSS Feed