Error description
When using dbms_advisor on a table in SYS or system schema, the following errors returned.
ORA-13600: Error encountered in Advisor
The QSM-00794: the statement can not be stored due to a violation of the invalid Table reference filter
Below is an example.
SQL> conn system/s
Connected.
SQL> begin
Dbms_advisor.quick_tune (
Advisor_name => dbms_advisor.sqlaccess_advisor,
Task_name => 'emp_quick_tune ',
Attr1 => 'select * from EMP where empno = 7788 ');
End;
/
Begin
*
Error at line 1:
ORA-13600: Error encountered in Advisor
The QSM-00775: the specified SQL statement cannot be stored in the workload due to invalid table references
ORA-06512: At "SYS. prvt_access_advisor", line 1501
ORA-06512: At "SYS. WRI $ _ adv_sqlaccess_adv", line 176
ORA-06512: At "SYS. prvt_advisor", line 2594
ORA-06512: At "SYS. dbms_advisor", line 726
ORA-06512: At Line 2
SQL> Create Table EMP (empno number );
Table created.
SQL> begin
Dbms_advisor.quick_tune (
Advisor_name => dbms_advisor.sqlaccess_advisor,
Task_name => 'emp_quick_tune ',
Attr1 => 'select * from EMP where empno = 7788 ');
End;
/
Begin
*
Error at line 1:
ORA-13600: Error encountered in Advisor
The QSM-00794: the statement can not be stored due to a violation of the invalid Table reference filter
ORA-06512: At "SYS. prvt_access_advisor", line 1501
ORA-06512: At "SYS. WRI $ _ adv_sqlaccess_adv", line 176
ORA-06512: At "SYS. prvt_advisor", line 2594
ORA-06512: At "SYS. dbms_advisor", line 726
ORA-06512: At Line 2
Cause of the problem
The quick_tune procedure performs an analysis and generates recommendations for a single SQL statement.
SQL access advisor maintains an internal list of non-tunable tables regardless of the contents of the invalid_table_list parameter.
The table owned by sys, system or any other pre-defined Oracle schema can be tuned and hence will return error.
Solution of the problem
Create the table in other Schama rather than sys, system or any other pre-defined Oracle schema and run quick_tune procedure.
SQL> Create Table arju. EMP (empno number );
Table created.
SQL> begin
Dbms_advisor.quick_tune (
Advisor_name => dbms_advisor.sqlaccess_advisor,
Task_name => 'emp_quick_tune ',
Attr1 => 'select * From arju. EMP where empno = 100 ');
End;
/
PL/SQL procedure successfully completed.
From: http://arjudba.blogspot.com/2008/10/ora-13600-qsm-00775-ora-06512-when.html