Re: Problems with executing the DBMS_SNAPSHOT Package

Hi Stephen,

The materialised view consists of the same query in both schemas.

The query is based on only one table. That table is present in both schemas; each schema owns its copy of the table concerned. It is not a synonym; I've already been down that road.

Thanks for the suggestion though. ;o)

Cheers.

James

stephen O'D wrote:
> 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 - 17:28:14 CDT