ORA-13600 QSM-00794錯誤解決方案

ORA-13600 QSM-00794錯誤解決方案

1、 本想使用dbms_advisor做一個sql access advisor測試，不料卻遇ORA-13600 QSM-00794報錯，如下：

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 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

注意上面標紅色部分，表的schema為system

 

2、上metalink上查原因，發現有文章ID：403358.1中做了詳細原因描述

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

 

看來原因是因為dbms_advisor.quick_tune不支援為SYS和SYSTEM兩個使用者的表的顧問功能了。

 

3、將測試的表改到其他使用者下試試

3.1 在litest這個使用者下建立表

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

3.2 重新用DBMS_ADVISOR.QUICK_TUNE使用sql access advisor功能

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.

注意上面標綠色部分，更換成了litest（非SYS或SYSTEM使用者了）

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

Oracle資料庫查看使用預設密碼的使用者資訊