Home » SQL & PL/SQL » SQL & PL/SQL » Refreshing Materialized View (oracle 10g)
Refreshing Materialized View [message #686659] Sun, 13 November 2022 21:21 Go to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
Dear Sir,
I have created a materialized view with COMPLETE refresh on DEMAND and trying to refresh it and the status is showing as RUNNING without any end or termination to the refreshing. The view on which the MV is created has reference to tables from remote database.

I tried to create a table with the same query and the table got created without any errors which the MV is just in RUNNING status for ever.

How should i debug the issue further.

Thank you
Re: Refreshing Materialized View [message #686662 is a reply to message #686659] Tue, 15 November 2022 01:11 Go to previous messageGo to next message
John Watson
Messages: 8916
Registered: January 2010
Location: Global Village
Senior Member
You could start by seeing if the refresh is actually running or hanging. THis will list all sessions that are hanging, and why:
select username,event,seconds_in_wait from v$session where wait_class <> 'Idle';
Re: Refreshing Materialized View [message #687482 is a reply to message #686662] Thu, 16 March 2023 01:05 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
It shows seconds_in_Wait as zero. This issues is still unresolved and need some help.
Re: Refreshing Materialized View [message #687484 is a reply to message #687482] Thu, 16 March 2023 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the event?

Re: Refreshing Materialized View [message #687485 is a reply to message #687484] Thu, 16 March 2023 01:45 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I am using a DBMS scheduler job to refresh all my MV's and the status of this job is now RUNNING nearly 24 hrs while i tried to create a table with the same query and it is created in 45 min.

username event                     seconds_in_wait
CTCOFF   SQL*Net message to client               0
CTCOFF   db file scattered read                  0
Re: Refreshing Materialized View [message #687486 is a reply to message #687485] Thu, 16 March 2023 02:04 Go to previous messageGo to next message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
I found now in USER_SCHEDULER_JOB_RUN_DETAILS the job is already stopped within 35 min with error message as REASON="Job slave process was terminated" while the USER_SCHEDULER_JOBS was still showing RUNNING until now. Further I need to check the alert log to get more information on why did the job terminate.
Re: Refreshing Materialized View [message #687487 is a reply to message #687486] Thu, 16 March 2023 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Further I need to check the alert log to get more information on why did the job terminate.

Correct.

Re: Refreshing Materialized View [message #687488 is a reply to message #687487] Thu, 16 March 2023 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that ERROR# and ADDITIONAL_INFO in USER_SCHEDULER_JOB_RUN_DETAILS will give you some information as well as USER_SCHEDULER_JOB_LOG.

Re: Refreshing Materialized View [message #687491 is a reply to message #687488] Mon, 20 March 2023 04:43 Go to previous messageGo to next message
CiaraDavid
Messages: 2
Registered: March 2023
Junior Member
Check the database logs to see if there are any error messages or problems with the MV. This may help determine exactly what is happening to the MV during the update. Try running the query used to update the MV manually and see if any errors occur. If the query runs without errors, the problem may be MV related. Check to see if there are any locks on the tables used in the MV. This can cause a delay in query execution and a RUNNING status for the MV.
Re: Refreshing Materialized View [message #687492 is a reply to message #687491] Mon, 20 March 2023 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68597
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Check to see if there are any locks on the tables used in the MV.

If there were some locks the waiting events (assuming there were correctly reported) would be different.

Re: Refreshing Materialized View [message #687497 is a reply to message #687491] Tue, 21 March 2023 07:50 Go to previous messageGo to next message
CiaraDavid
Messages: 2
Registered: March 2023
Junior Member
Thank you for approving the comment.

[Updated on: Tue, 21 March 2023 07:50]

Report message to a moderator

Re: Refreshing Materialized View [message #687498 is a reply to message #687497] Tue, 21 March 2023 07:58 Go to previous message
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
Senior Member
When i executed the same query to create a table, it is done in 45 min while i try to refresh the MV it just remains RUNNING. I found a way to avoid using database links by replicating those transactions in this same schema and by this way the MV is refreshing quickly in 5 min.
Previous Topic: Rescheduling installments by distributing amounts
Next Topic: convert xml column value into columns
Goto Forum:
  


Current Time: Tue Feb 27 20:24:53 CST 2024