OraFAQ Forum: SQL & PL/SQL » Refreshing Materialized View

Home » SQL & PL/SQL » SQL & PL/SQL » Refreshing Materialized View (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Refreshing Materialized View [message #686659]

Sun, 13 November 2022 21:21

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

Report message to a moderator

Re: Refreshing Materialized View [message #686662 is a reply to message #686659]

Tue, 15 November 2022 01:11

John Watson
Messages: 8881
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';

Report message to a moderator

Re: Refreshing Materialized View [message #687482 is a reply to message #686662]

Thu, 16 March 2023 01:05

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.

Report message to a moderator

Re: Refreshing Materialized View [message #687484 is a reply to message #687482]

Thu, 16 March 2023 01:36

Michel Cadot
Messages: 68512
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And the event?

Report message to a moderator

Re: Refreshing Materialized View [message #687485 is a reply to message #687484]

Thu, 16 March 2023 01:45

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

Report message to a moderator

Re: Refreshing Materialized View [message #687486 is a reply to message #687485]

Thu, 16 March 2023 02:04

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.

Report message to a moderator

Re: Refreshing Materialized View [message #687487 is a reply to message #687486]

Thu, 16 March 2023 02:24

Michel Cadot
Messages: 68512
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.

Report message to a moderator

Re: Refreshing Materialized View [message #687488 is a reply to message #687487]

Thu, 16 March 2023 02:29

Michel Cadot
Messages: 68512
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.

Report message to a moderator

Re: Refreshing Materialized View [message #687491 is a reply to message #687488]

Mon, 20 March 2023 04:43

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.

Report message to a moderator

Re: Refreshing Materialized View [message #687492 is a reply to message #687491]

Mon, 20 March 2023 05:11

Michel Cadot
Messages: 68512
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.

Report message to a moderator

Re: Refreshing Materialized View [message #687497 is a reply to message #687491]

Tue, 21 March 2023 07:50

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

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.

Report message to a moderator

Previous Topic:	Rescheduling installments by distributing amounts
Next Topic:	convert xml column value into columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Oct 04 16:16:34 CDT 2023