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

 

注意上面标红色部分,表的schemasystem

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数据库查看使用默认密码的用户信息

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ce463157c6b2cf81a9c6355f14be24a5.html