RE: Locking over a DB link

From: Goulet, Richard <>
Date: Tue, 9 Dec 2008 16:11:32 -0500
Message-ID: <>

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
 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.  

[] 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

Received on Tue Dec 09 2008 - 15:11:32 CST

Original text of this message