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.
-------- -------- ---- ----- ---------- ----------
---------- ----------
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
