Error When Refreshing Materialized View [message #667244] |
Sun, 17 December 2017 06:24 |
|
ANU_IN
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
as
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 #667255 is a reply to message #667245] |
Mon, 18 December 2017 06:33 |
Bill B
Messages: 1971 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
|
|
|