Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View: A Question (Oracle 10g)
Materialized View: A Question [message #336854] Tue, 29 July 2008 04:17 Go to next message
handii
Messages: 4
Registered: July 2008
Location: India
Junior Member
Hi,

There is one Legacy database DB1 and other Oracle Applications Database DB2. I created a Read-Only Materialized View in Legacy Database DB1 which fetches the data from Oracle Apps Database DB2 Tables.

Now my question is,

When there will be a downtime for oracle Applications Database DB2 then will new Materialized View be accessible in Legacy Database DB1?

Regards and Thanks in Advance!!
P2
Re: Materialized View: A Question [message #336863 is a reply to message #336854] Tue, 29 July 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, with data from the last refresh.

Regards
Michel
Re: Materialized View: A Question [message #336886 is a reply to message #336863] Tue, 29 July 2008 05:13 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... because materialized views contain data (because they store result of a query in a separate schema object), as opposed to "ordinary" views which are "stored queries" and, if there's no access to underlying tables, there's no data in a view.
Re: Materialized View: A Question [message #337197 is a reply to message #336886] Wed, 30 July 2008 02:31 Go to previous messageGo to next message
handii
Messages: 4
Registered: July 2008
Location: India
Junior Member
Will the materialized view will get refreshed once the system is up again?
Re: Materialized View: A Question [message #337200 is a reply to message #337197] Wed, 30 July 2008 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes, if an automatic refresh is defined on it and if the job is not broken and if job_queue_processes is greater than 0.

Regards
Michel

[Updated on: Wed, 30 July 2008 02:36]

Report message to a moderator

Re: Materialized View: A Question [message #337302 is a reply to message #337200] Wed, 30 July 2008 08:48 Go to previous messageGo to next message
handii
Messages: 4
Registered: July 2008
Location: India
Junior Member
Hi Michel,

Thanks for your response.

What do you mean by a 'JOB is not broken'?

We have following Refresh method for our Materialized view,

REFRESH COMPLETE
START WITH TO_DATE('29-JUL-2008 00:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE + 1

So as per your reponse, Can I assume that after the system is restored, on next MIDNIGHT, our Materialized View will be refreshed?

Regards,
Pranit

[Updated on: Wed, 30 July 2008 08:48]

Report message to a moderator

Re: Materialized View: A Question [message #337303 is a reply to message #337302] Wed, 30 July 2008 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For your 2 questions, query dba_jobs and see next_date and broken columns for the refresh job.

Regards
Michel
Re: Materialized View: A Question [message #337312 is a reply to message #337303] Wed, 30 July 2008 09:07 Go to previous messageGo to next message
handii
Messages: 4
Registered: July 2008
Location: India
Junior Member
Ok, then my question is,
will the BROKEN column in dba_jobs table be updated to 'N' when the interfacing instance (where Referenced tables exist) is down for refresh or some other reason.

Just to remind, in my question I asked that my materialized View is on a different instance than the instance where referenced tables are lying.

Hope I am clear or let me know.

Thanks!
Pranit
Re: Materialized View: A Question [message #337313 is a reply to message #336854] Wed, 30 July 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>will the BROKEN column in dba_jobs table be updated to 'N' when the interfacing instance (where Referenced tables exist) is down for refresh or some other reason.
WHY?
What would change the column?
Why should the value in the column change because the database was bounced?
Re: Materialized View: A Question [message #337320 is a reply to message #337312] Wed, 30 July 2008 10:03 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
BROKEN just indicates that either the job was set to broken, either it fails more X times (I currently don't remember X, I think it is 16).
It BROKEN is Y Oracle will no more try to execute the job until you explicitly set it to N or you manually successfully execute the job.

Regards
Michel
Previous Topic: how to delete rows from the table.
Next Topic: ORA-30625: method dispatch on NULL SELF argument is disallowed
Goto Forum:
  


Current Time: Sat Feb 15 02:27:42 CST 2025