Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW

From: Robbert van der Hoorn <reply_at_forum.only>
Date: Thu, 5 Oct 2006 20:48:30 +0200
Message-ID: <45255393$0$4513$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1160070560.741694_at_bubbleator.drizzle.com...

> Robbert van der Hoorn wrote:
>> <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
>
> There is also a demo in Morgan's library at www.psoug.org under
> DBMS_ADVISOR.
> -- 
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group

Of course!

Thanks,

Robbert Received on Thu Oct 05 2006 - 13:48:30 CDT