Re: Problems with executing the DBMS_SNAPSHOT Package

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

When you say "older development schema" my guess is that it is a previous version of Oracle and it is possible the rules have changed. Post the DDL and identify the versions.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org