Monday, April 11, 2011

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

When creating materialized view, do not query directly from database link.
Below query will be failed:
CREATE MATERIALIZED VIEW "ABC"
TABLESPACE "XYZ"
PCTFREE
10 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
NOLOGGING
PARALLEL (DEGREE 4) CACHE USING NO INDEX
REFRESH
FORCE START WITH to_date('04-11-2011 11:58:35','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1/24 AS select * from abc@dblink
You can create a view first and select from the view, or:

CREATE MATERIALIZED VIEW "ABC"
TABLESPACE "XYZ"
PCTFREE
10 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT)
NOLOGGING
PARALLEL (DEGREE 4) CACHE USING NO INDEX
REFRESH
FORCE START WITH to_date('04-11-2011 11:58:35','MM-dd-yyyy hh24:mi:ss') NEXT sysdate + 1/24 AS select * from (select * from abc@dblink)