Re: QSM-00781 on DBMS_ADVISOR.TUNE

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 06 Oct 2006 09:41:38 -0700
Message-ID: <1160152896.121354@bubbleator.drizzle.com>

thtsang_yh_at_yahoo.com.hk wrote:
> Robbert van der Hoorn 寫道：
>

>> 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

>
> 1) Do you have the ADVISOR role?
> Not sure. I have DBA role. I also granted ADVISOR role to myself with
> SYS successfully. But I cannot see ADVISOR role in SESSION_ROLES and
> USER_ROLE_PRIVS.
> I also tried the samples with sys and system, the same thing happens.
>
> 2) MV's on DUAL are impossible, for DUAL has no primary key.
> It is possible to create a primary key on DUAL.
> BTW, my CREATE MATERIALIZED VIEW statement do work even without PK.
> As I've said, the problem happens on other table. I am just using a
> table that exists on every Oracle DB.
>
> 3) Don't assign anything to name. It's returned.
> Why can't I assign anything to INOUT parameter?
> Anyway, the same problem occur when I set nothing to it.
>
> 4) Try this one (works for me)
> Same error.
>
> I also get the same error with Morgan's sample. I have neither SH
> schema nor the HELP table. But I've created the tables and made sure
> the CREATE MV statements do work.
>
> According to DBMS_ADVISOR docs, there are 4 possible values for
> EXECUTION_TYPE. But the error message listed two only, missing out the
> ones related to MV. Can this be the cause / symptom of the problem?

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 6 09:40:58 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> DECLARE
   2 vname VARCHAR2(30); -- 'name' is a reserved word    3 mv_sql VARCHAR2(1000);
   4 BEGIN
   5 mv_sql := 'create materialized view mvtest as select srvr_id, network_id, status from servers

  where srvr_id=1';
   6    DBMS_ADVISOR.TUNE_MVIEW(vname, mv_sql);
   7    dbms_output.put_line(vname);

8 END;
9 /
TASK_3747 PL/SQL procedure successfully completed.

SQL> It is generally a bad idea to name variables after reserved words.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Fri Oct 06 2006 - 11:41:38 CDT

Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW