| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: [RE: locking issue. Please help]
pLEASE checkup is there enough space in the rollback segment ( undo) to take the before image of the delete. ( is Ur delete statement deletes a whole lot of data ?
Vadim Gorbounov <vgorbounov_at_simplyengineering.com> wrote:
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.
-- 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 ListsReceived on Mon Feb 05 2001 - 20:01:57 CST
--------------------------------------------------------------------
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). ____________________________________________________________________ Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: S Jayakumar INET: sjayakumar2000_at_usa.net 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).
 |
 |