Re: QSM-00781 on DBMS_ADVISOR.TUNE_MVIEW

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? Received on Thu Oct 05 2006 - 20:51:52 CDT