RE: question on ORA-02049: timeout: distributed transaction waiting for lock

From: Yavor Ivanov <Yavor_Ivanov_at_stemo.bg>
Date: Mon, 9 Mar 2009 19:01:05 +0200
Message-ID: <BD17E2E69E17C64A9684C940EB580E030111A7AED338_at_stemodc1.stemo.local>

I could not understand what is "originated from Test ". Anyway, the lock should be on the destination, where the MV is. When updateable MV refreshes, it locks the MV table (no matter if this is full or fast refresh). If the refresh cannot lock the table within DISTRIBUTED_LOCK_TIMEOUT seconds, it will give up with this error.

Regards,
Yavor

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Guang Mei Sent: Monday, March 09, 2009 5:57 PM
To: oracle-l_at_freelists.org
Subject: question on ORA-02049: timeout: distributed transaction waiting for lock

Hi,

Oracle 10gR2.
We have a croned MV refresh process (originated from Test, copying T1 data from Production to Test) that a couple of time fails with this message:

BEGIN dbms_mview.refresh('T1'); END;

*
ERROR at line 1:

ORA-12008: error in materialized view refresh path
ORA-02049: timeout: distributed transaction waiting for lock
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

My question is, does the lock is on the T1 of Test or Production DB? I would think there was some process that was updating T1 table on Production while this MV refresh started, Am I right?

TIA Guang Mei
www.eversave.com<http://www.eversave.com>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 09 2009 - 12:01:05 CDT