Home » SQL & PL/SQL » SQL & PL/SQL » Error When Refreshing Materialized View (Oracle 10g)
Error When Refreshing Materialized View [message #667244] Sun, 17 December 2017 06:24 Go to next message
Messages: 1
Registered: December 2017
Junior Member
I am facing an issue with materialized view refresh. Below is the problem description:-

There are 2 databases - DB1(SchemaX) and DB2(SchemaY).
In DB1(SchemaX), I have a table Nlabel for whcih a materialized view with fast refresh is created in DB2(SchemaY)using a DB link.
For example:- MV is created in DB2(SchemaY) as below

create materialized view mv_nlabel
build immediate
refresh fast
on demand
select * from nlabel.SchemaX@dblnk_DB1;
Ideally the MV should be refreshed automatically, but i am getting below error:

ora-00604 error occurred at recursive sql level 2 in materialized view.ora-12537 TNS connection closed.

Appreciate if anyone could help me with the solution and step to fix this error.
Re: Error When Refreshing Materialized View [message #667245 is a reply to message #667244] Sun, 17 December 2017 07:10 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

An ORA-00604 should be followed by other messages. If you show what you did and what happened (use SQL*Plus, and copy/paste) someone may be able to advise.

If you want an automatic refresh, then use the START WITH ... NEXT ... clause.
Re: Error When Refreshing Materialized View [message #667255 is a reply to message #667245] Mon, 18 December 2017 06:33 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
refresh fast
on demand

doesn't mean refresh it if anyone uses it. It means refresh it when it is scheduled and for that you need to build it with the START WITH ... NEXT clause as John said above. The clause makes a dba_job that will run on the schedule you specified
Previous Topic: CREATE VIEW with "SELECT * FROM" several tables
Next Topic: Oracle 12c injection
Goto Forum:

Current Time: Mon Sep 21 11:16:37 CDT 2020