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

Error Description
When using DBMS_ADVISOR on a table in SYS or SYSTEM schema, the following errors returned.

ORA-13600: error encountered in Advisor
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
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
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 = 7788');
END;
/


PL/SQL procedure successfully completed.


FROM: http://arjudba.blogspot.com/2008/10/ora-13600-qsm-00775-ora-06512-when.html


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值