ORA-13600, QSM-00775, ORA-06512 when running dbms_advisor

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.