ORA-13600 QSM-00794 error Solution

ORA-13600 QSM-00794 error Solution

1, wanted to use dbms_advisor to do a SQL access advisor test, but unexpectedly encountered ORA-13600 QSM-00794 error, as follows:

SQL> DECLARE 2 task_name VARCHAR2 (200 ); 3 BEGIN 4 task_name: = 'my _ task '; 5 DBMS_ADVISOR.QUICK_TUNE (DBMS_ADVISOR.SQLACCESS_ADVISOR, 'My _ task ', 6 'select * from system. litest_8 where object_id = 20 '); 7 END; 8/ DECLARE * 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 1809 ORA-06512: at "SYS. WRI $ _ ADV_SQLACCESS_ADV", line 180 ORA-06512: at "SYS. PRVT_ADVISOR", line 3678 ORA-06512: at "SYS. DBMS_ADVISOR", line 711 ORA-06512: at line 5

Note that the table schema is system

 

2. Check the cause on metalink and find the detailed cause description in Article ID: 403358.1.

SQL Access Advisor maintains an internal list of non-tunable tables regardless of the contents The INVALID_TABLE_LIST parameter. No table that is owned by SYS, SYSTEM or any other pre-defined Oracle schema can be tuned.

 

The reason is that dbms_advisor.quick_tune does not support the advisor function for the tables of SYS and SYSTEM users.

 

3. Change the test table to another user.

3.1 create a table under the litest user

SQL> create table litest. litest_8 asselect * from system. litest_8

3.2 use DBMS_ADVISOR.QUICK_TUNE again to use the SQL access advisor Function

SQL> DECLARE 2 task_name VARCHAR2 (200 ); 3 BEGIN 4 task_name: = 'my _ task '; 5 DBMS_ADVISOR.QUICK_TUNE (DBMS_ADVISOR.SQLACCESS_ADVISOR, 'My _ task ', 6 'select * from litest. litest_8 where object_id = 20 '); 7 END; 8/   PL/SQL procedure successfully completed.

Note that the green part above is replaced with litest (not a SYS or SYSTEM user)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

View the user information using the default password in the Oracle database