Tag: SYS.DBMS_SNAPSHOT_UTL

ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960

Posted on by Osama Mustafa in Database
This Error Appear when you try to create MATERIALIZED VIEW On remote View 

First you have to know it

 Bug 5583712  ORA-942 on create materialized view on remote view

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected
Platforms affected Generic (all / most platforms affected

 You can check Metalink Doc : 5583712.8 


But I would Share this solution All you Have to do is follow the below steps :

CREATE   MATERIALIZED VIEW RHC
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND START WITH SYSDATE+1 NEXT SYSDATE + 8/24
  AS SELECT * FROM vw_RHC@remotedb;
 

ORA-00942: TABLE OR VIEW does NOT exist
ORA-06512: AT “SYS.DBMS_SNAPSHOT_UTL”, line 960
ORA-06512: AT line 1

The Solution is :

CREATE   MATERIALIZED VIEW RHC
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND START WITH SYSDATE+1  NEXT   SYSDATE + 8/24
  AS   AS SELECT * FROM (SELECT * FROM vw_employees@remotedb);

Materialized VIEW created.

Thank you
Osama mustafa