QSM-00781 on DBMS_ADVISOR.TUNE

From: <thtsang_yh_at_yahoo.com.hk>
Date: 5 Oct 2006 02:41:10 -0700
Message-ID: <1160041270.105972.179680@h48g2000cwc.googlegroups.com>

I am trying to tune a MVIEW and got the following error when running the statement:

  1 declare
  2 name varchar2(30);
  3 mv_sql varchar2(1000);
  4 begin
  5 name:='TUNE_USER_POSSIBLE_ROLES_MV';   6 mv_sql := 'CREATE MATERIALIZED VIEW MY_MV   7 AS SELECT COUNT(*) FROM DUAL';
  8 DBMS_ADVISOR.TUNE_MVIEW(NAME, mv_sql);   9* END;
10 /
declare
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00781: the specified value MVIEW_ONLY is invalid for parameter EXECUTION_TYPE. Valid keywords are FULL, INDEX_ONLY

ORA-06512: at "SYS.PRVT_ADVISOR", line 2931
ORA-06512: at "SYS.DBMS_ADVISOR", line 287
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 604
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1061
ORA-06512: at "SYS.DBMS_ADVISOR", line 754
ORA-06512: at line 8

I tried different CREATE MATERIALIZED VIEW statements, but all give the error. In this post, I used a MV as simple as possible to make sure the error is not caused by syntax error. (i.e., don't ask me why I create a useless MV)

DB version: Oracle 10g R2 on Linux x86 Received on Thu Oct 05 2006 - 04:41:10 CDT

QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW