Wednesday, May 16, 2012

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

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 affectedVersions < 11
Versions confirmed as being affected
Platforms affectedGeneric (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

7 comments:

  1. Grazie OSAMA
    very very thankyou :)

    ReplyDelete
  2. we hit this bug in prod but not other environments, so very thank you for the workaround.

    ReplyDelete
  3. U really rocks...

    ReplyDelete
  4. Ditto Anonymous' comment from 1/31/14. Funny how it likes production environments best.

    ReplyDelete