Problems with executing the DBMS_SNAPSHOT Package

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 Received on Wed Oct 11 2006 - 15:30:50 CDT