Home » Server Options » Replication » Hange while refreshing materialized view (Solaris 5.9, Oracle 10.2.0.2)
Hange while refreshing materialized view [message #359667] Mon, 17 November 2008 23:31 Go to next message
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
Re: Hange while refreshing materialized view [message #359901 is a reply to message #359667] Tue, 18 November 2008 12:04 Go to previous messageGo to next message
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
Re: Hange while refreshing materialized view [message #359937 is a reply to message #359667] Tue, 18 November 2008 21:57 Go to previous messageGo to next message
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
Re: Hange while refreshing materialized view [message #360114 is a reply to message #359937] Wed, 19 November 2008 11:56 Go to previous message
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 


3/

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:
  


Current Time: Thu Mar 28 04:26:07 CDT 2024