OraFAQ Forum: Replication » Hange while refreshing materialized view

Home » Server Options » Replication » Hange while refreshing materialized view (Solaris 5.9, Oracle 10.2.0.2)

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

Hange while refreshing materialized view [message #359667]

Mon, 17 November 2008 23:31

harshkumar
Messages: 34
Registered: February 2008
Location: Delhi

Member

Hello Babu,

I faced the problem in refreshing the two materialized view. I am trying with 'complete refresh' but it hanged. If i tried to drop the materialized view then also it hanged and not able to drop the materialized views.

I am having 6 MV's and rest 4 are refreshing without any problem.

Can you please help me to solve the issue.

Thanks,
Harsh

Report message to a moderator

Re: Hange while refreshing materialized view [message #359901 is a reply to message #359667]

Tue, 18 November 2008 12:04

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Hello,

Thanks a lot.

1/ how many records having base table?
2/ during refresh any database lock/dead lock?
3/ working in basic/advanced replicaion?
4/ post your refresh script
5/ during refresh what is the result of below query

select * from v$mvrefresh;

select * from V$REPLPROP;

select * from V$REPLQUEUE ;

Then database version??

Babu

Report message to a moderator

Re: Hange while refreshing materialized view [message #359937 is a reply to message #359667]

Tue, 18 November 2008 21:57

harshkumar
Messages: 34
Registered: February 2008
Location: Delhi

Member

Hi Babu,

Thanks a lot for the reply.

I am trying to drop the 2 MV's but then also got the hanging. The below are the answers:

1/ how many records having base table?

MV1 -> 731525     MV2-> 750397

2/ during refresh any database lock/dead lock?

Transaction lock in Exclusive mode and wait event "enq: JI contention.

3/ working in basic/advanced replicaion?

Basic replication

4/ post your refresh script

I tried to refresh through OEM as well as manually.

EXEC DBMS_MVIEW.REFRESH('PART_TRACK_FRAME_EX', 'A', '', TRUE, FALSE, 0,0,0, FALSE);

5/ during refresh what is the result of below query

select * from v$mvrefresh;

144	1	GPS_MM_LDC_FRAME	PART_TRACK_FRAME_2D_EX

select * from V$REPLPROP;

No Records

select * from V$REPLQUEUE ;

0 0 0

Please suggest.

Thanks,
Harsh

Report message to a moderator

Re: Hange while refreshing materialized view [message #360114 is a reply to message #359937]

Wed, 19 November 2008 11:56

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Hi,

Sorry for delay response.

Yes I agree it takes some time but please reply me below details.

1/ Why your not going fast/force refresh?

2. Connect Sys user.

Show parameter job

show parameter query

show parameter opt

Quote:

EXEC DBMS_MVIEW.REFRESH('PART_TRACK_FRAME_EX', 'A', '', TRUE, FALSE, 0,0,0, FALSE);

a/ I think by default atomic_refresh is TRUE. May i know any specific reason why your using FALSE??

b/ Did you discussed with oracle support (metalink) to change automic_refresh parameter??

Thank you in advance.

Babu

[Updated on: Wed, 19 November 2008 14:33]

Report message to a moderator

Previous Topic:	Replication
Next Topic:	Replication between oracle 10g & 11g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 23:17:37 CST 2024