RE: Locking over a DB link

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Tue, 9 Dec 2008 16:39:48 -0500
Message-ID: <23C4836D8E9C5F4280A66C0C247BC16F20D48A02@US-BOS-MX011.na.pxl.int>

If memory serves correctly, yes. The reason is that it's an exclusive lock. And the "set transaction read-only" doesn't work as I remember because it sets the local system as well preventing the create table. Been a long time since I banged my head into this puppy so I can be dead wrong.  

Dick Goulet
Senior Oracle DBA
PAREXEL International
978.313.3426
 information transmitted in this communication is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please destroy any copies, contact the sender and delete the material from any computer.  

---

From: jheinrich_at_heinrichfamily.com [mailto:jheinrich_at_heinrichfamily.com] On Behalf Of Jason Heinrich
Sent: Tuesday, December 09, 2008 4:26 PM To: Goulet, Richard
Cc: Oracle List
Subject: Re: Locking over a DB link

Hmm. I think I need to brush up on the different types of locks. In the meantime, could this remote lock prevent the logical standby from applying DML to those tables?

On Tue, Dec 9, 2008 at 3:11 PM, Goulet, Richard <Richard.Goulet_at_parexel.com> wrote:

        OH DEAR, talk about going back in time!! Yes, this type of transaction will cause a lock on the remote database, similar to if you were doing the same thing on the local database. Oracle does this so that no one changes the definition of the table in mid stream. Now if I remember correctly the lock does not get released till either the local session ends or the database link is closed.          

	Dick Goulet 
	Senior Oracle DBA 
	PAREXEL International 
	978.313.3426 
	 information transmitted in this communication is intended only

for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please destroy any copies, contact the sender and delete the material from any computer.          

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jason Heinrich

	Sent: Tuesday, December 09, 2008 3:48 PM
	To: Oracle List
	Subject: Locking over a DB link
	
	
	I was wondering if it was possible for SQL similar to the

following to cause a lock to be created on the remote database:         

	insert into localtable
	(select columns from remotetable_at_dblink where <some condition>);
	
	My first reaction was no, but I thought I remembered locking

oddities over links being discussed before. So far I haven't found anything useful in my searches though.         

	-- 
	Jason Heinrich
	

-- 
Jason Heinrich


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

Received on Tue Dec 09 2008 - 15:39:48 CST