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

8 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
  5. My trial to resolve [ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 960] issue with

    [SELECT * FROM (SELECT * FROM vw_employees@remotedb);] did not work. Reason unknown to me then I tried the same approach in different way.

    1. Created a view for remote DB in local DB.
    Create View MyView AS SELECT * FROM vw_employees@remotedb;

    2. Created materialized view out of local view.
    Create Materialized View MyMV
    AS Select * from MyView;

    In essence the same solution in different approach. It might had been my local DB is 12c and Remote DB is in 10g.

    Thanks for the solution.

    ReplyDelete