Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problems with executing the DBMS_SNAPSHOT Package

Re: Problems with executing the DBMS_SNAPSHOT Package

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 11 Oct 2006 14:23:31 -0700
Message-ID: <1160601811.487357.152680@h48g2000cwc.googlegroups.com>

Jimbo1 wrote:
> Hello there,
>
> I'm currently experiencing some strange behaviour on Oracle 10g R2.
>
> I'm trying to run the DBMS_MVIEW.REFRESH procedure in a particular
> schema, and I'm getting the following error:
>
> EXEC DBMS_MVIEW.REFRESH( 'MV_TEST1', method => 'C' );
>
> ERROR at line 1:
> ORA-00942: table or view does not exist
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1883
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2089
> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2058
> ORA-06512: at line 1
>
> The MView concerned is owned by the schema I'm running this procedure
> in.
>
> Now, there is another (older) development schema that contains the same
> MView. When I run the refresh procedure in that schema, I get:
>
> SQL> EXEC DBMS_MVIEW.REFRESH( 'MV_TEST1', method => 'C' );
>
> PL/SQL procedure successfully completed.
>
> In terms of privileges and synonyms, there is a public synonym that
> points to the DBMS Snapshot Package, i.e.
>
> SQL> SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME IN ('DBMS_MVIEW',
> 'DBMS_SNAPSHOT');
>
> OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
> ------ ------------- ------------ -------------- ---------------
> PUBLIC DBMS_MVIEW SYS DBMS_SNAPSHOT
> PUBLIC DBMS_SNAPSHOT SYS DBMS_SNAPSHOT
>
>
> The PUBLIC user/role has been granted execute privs on DBMS_SNAPSHOT,
> i.e.
>
> SQL> SELECT * FROM ALL_TAB_PRIVS_RECD WHERE GRANTEE = 'PUBLIC' AND
> TABLE_NAME = 'DBMS_SNAPSHOT';
>
> GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
> -------- ------ -------------- -------- ---------
> PUBLIC SYS DBMS_SNAPSHOT SYS EXECUTE
>
>
> If we're running under definer rights mode (which I assume we are when
> Oracle-Supplied packages are created), then what can be seen in the SYS
> schema by the DBMS_SNAPSHOT package should not be impacted by which
> schema the package is run from.
>
> Have any of you seen an error like this before? If so, please can you
> offer any suggestions on how to resolve it?
>
> Thanks in advance.
>
> James

Are the underlying tables of the MView in the same schema or in a different schema? Are there synonyns and grants to the MView schema for them? Received on Wed Oct 11 2006 - 16:23:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US