Re: Problems with executing the DBMS_SNAPSHOT Package

Hi Daniel,

Wrong; it's a schema on the same 10g R2 database. The schema was used for the development of a separate release.

Anyway, I've since discovered what the problem was, and will post the solution when I get a minute. I'm in the unfortunate position of not being able to post to this group from work, and I'm sitting here now having forgotten the name of the dictionary view I found a useful pointer in regarding what was wrong. In a nutshell, it had nothing to do with DBMS_SNAPSHOT access rights and synonyms.

I'll type up a proper reply/solution tomorrow at work, and paste it in here when I get home.

Thanks for the reply anyway.

James

DA Morgan 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
>
> 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
Received on Thu Oct 12 2006 - 18:36:24 CDT