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

Tue, 06/02/2009 - 06:21 — birijan

I've been granted to select on view on remote database and I want to keep data locally on my database system using materialized view but when I tried to create materialized view I got following error:

CREATE   MATERIALIZED VIEW employees

  BUILD IMMEDIATE

  USING INDEX

  REFRESH COMPLETE ON DEMAND START WITH SYSDATE+0 NEXT SYSDATE + 6/24

  USING DEFAULT LOCAL ROLLBACK SEGMENT

  DISABLE QUERY REWRITE

  AS SELECT * FROM vw_employees@remotedb;

ORA-00942: TABLE OR VIEW does NOT exist

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

ORA-06512: AT line 1

After querying about error I notice that the error was due to the remote database link on view and I change query from SELECT * FROM vw_employees@remotedb to SELECT * FROM (SELECT * FROM vw_employees@remotedb) and worked perfectly for me.

SQL> CREATE   MATERIALIZED VIEW employees

  BUILD IMMEDIATE

  USING INDEX

  REFRESH COMPLETE ON DEMAND START WITH SYSDATE+0 NEXT SYSDATE + 6/24

  USING DEFAULT LOCAL ROLLBACK SEGMENT

  DISABLE QUERY REWRITE

  AS SELECT * FROM (SELECT * FROM vw_employees@remotedb);

Materialized VIEW created.

Database

birijan's blog
Login to post comments