Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW

<thtsang_yh_at_yahoo.com.hk> schreef in bericht news:1160041270.105972.179680_at_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
>

1. Do you have the ADVISOR role?
2. MV's on DUAL are impossible, for DUAL has no primary key.
3. Don't assign anything to name. It's returned.
4. Try this one (works for me):

log in as system
set serveroutput on

run this:

declare
name varchar2(30);
mv_sql varchar2(1000);
begin
 mv_sql := 'create materialized view mvtest as select seq, topic, info from help where seq=1';
DBMS_ADVISOR.TUNE_MVIEW(NAME, mv_sql);
dbms_output.put_line(name);
 END;
/

Robbert van der Hoorn Received on Thu Oct 05 2006 - 10:32:58 CDT