Home > TroubleShooting > Refresh Materialized View Hung

Refresh Materialized View Hung

Problem Summary:

– Execute refresh the mview, it held and had no return for a long time. Finally aborting the refreshment.
SQL> exec dbms_mview.refresh(‘CMS_PROG_PROMO_IMG_MV’, ‘C’);

– Perform session trace

SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8′;

– The session trace shows that the following message
WAIT #4: nam=’enq: JI – contention’ ela= 2934487 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545181045005
WAIT #4: nam=’enq: JI – contention’ ela= 1962539 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545183007934
WAIT #4: nam=’enq: JI – contention’ ela= 2939193 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545185947369
*** 2009-12-08 12:07:27.579
WAIT #4: nam=’enq: JI – contention’ ela= 1962673 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545187910260
WAIT #4: nam=’enq: JI – contention’ ela= 2939132 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545190849689
WAIT #4: nam=’enq: JI – contention’ ela= 1962711 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545192812606
WAIT #4: nam=’enq: JI – contention’ ela= 2939196 name|mode=1246298118 view object #=63659 0=0 obj#=-1 tim=16545195752019

– The wait event means (enq: JI – contention) that Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view

Problem Caused: There is an another session holding the materialized view

Solution to fix the problem: Kill the holding session

Advertisements
Categories: TroubleShooting
  1. July 10, 2013 at 6:45 pm

    Amazing issues here. I’m very glad to peer your post. Thank you a lot and I’m looking forward
    to touch you. Will you kindly drop me a mail?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: