Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> locking issue. Please help

locking issue. Please help

From: sonia pajerowski <spajerowski_at_yahoo.com>
Date: Fri, 02 Feb 2001 09:55:19 -0800
Message-ID: <F001.002A9330.20010202092613@fatcity.com>

I have a trigger on a table which deletes data on a similar table on a remote database. When the delete operation is performed whole database gets locked and nothing can be done unless both the local and remote databases are shutdown. The table also has insert and update triggers which perform same operation on the remote db but they seem to work fine.

set linesize 80
 set pagesize 66
column lmode heading 'Lock|Held' format a4 column request heading 'Lock|Req.' format a4 column username format a10 heading "Username" column tab format a30 heading "Table Name" column LAddr heading "ID1 - ID2" format a16 column Lockt heading "Lock|Type" format a4 select nvl(S.USERNAME,'Internal') username, decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20), 'Rollback Segment')) tab,
decode(L.LMODE,1,'NoLk', 2,' RS ', 3,' RX ', 4,' S ', 5,' SRX', 6,' X ','NONE') lmode,
decode(L.REQUEST,1,'NoLk', 2,' RSh ', 3,' RX ', 4,' S ', 5,' SRX', 6,' X ','NONE') request,
 l.id1||'-'||l.id2 Laddr, l.type Lockt from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
where L.SID = S.SID and T1.OBJ# =
decode(L.ID2,0,L.ID1,1) and U1.USER# = T1.OWNER# and S.TYPE != 'BACKGROUND' order by 1,2,5
/

                                         Lock Lock    
             Lock
Username   Table Name                     Held Req.
ID1 - ID2        Type

---------- ------------------------------ ---- ----
---------------- ---- SMSWEB Rollback Segment X NONE 131089-324 TX SMSWEB SMSWEB.EVENTTIMES RX NONE 3846-0 TM SMSWEB SYS.UNDO$ NoLk NONE 15-0 DX

I also ran this statement and got the following.

elect * from v$lock where type='TX' and lmode>0     ;
ADDR     KADDR     SID TYPE         ID1        ID2    
  Held       Req.

-------- -------- ---- ----- ---------- ----------
---------- ----------

     CTIME BLOCK
---------- ----------

016E2154 016E2228 12 TX 131089 324 ########## ##########

       653          0                                 
            



Thanks

Sonia P.



Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: sonia pajerowski
  INET: spajerowski_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Feb 02 2001 - 11:55:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US