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

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

RE: locking issue. Please help

From: Vadim Gorbounov <vgorbounov_at_simplyengineering.com>
Date: Mon, 05 Feb 2001 14:53:49 -0800
Message-ID: <F001.002AB615.20010205144707@fatcity.com>

        Hi, Sonia,
        Just some ideas.
        Did you try to delete from both tables WITHOUT trigger? Could it be
delete itself takes a lot of time? It's not clear, do you issue the first select on the same machine, where you do delete? I would do the same but on another machine - may be, somebody already has a lock on the table/row.
        Vadim Gorbounov
        Oracle DBA


-----Original Message-----

Sent: Friday, February 02, 2001 12:26 PM To: Multiple recipients of list ORACLE-L

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.

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

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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vadim Gorbounov INET: vgorbounov_at_simplyengineering.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 Mon Feb 05 2001 - 16:53:49 CST

Original text of this message

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