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
Received on Thu Oct 05 2006 - 12:49:22 CDT
