OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Lock

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lock [message #38994]

Wed, 05 June 2002 02:40

Nicola Farina
Messages: 63
Registered: October 2001

Member

(Maybe it is a little ot.. excuse me..)

We have a program "freezed" on a row (the database is 8.1.7) of a table.
This row seems locked (when we try to delete it we wait wait wait..)
Is there any way to see the lock in the db (to kill the locking session, if possible)?
Thanks a million bye
Nicola

Report message to a moderator

Re: Lock [message #38996 is a reply to message #38994]

Wed, 05 June 2002 07:07

Grant
Messages: 578
Registered: January 2002

Senior Member

I have used this for years and works for 8i also:

REM =====================================================================
REM Procedure : lockv7
REM Author : Herve Delbarre
REM Subject : List of locks by users(except MR)
REM Oracle db : V7.
REM =====================================================================
SET ECHO OFF
SET TERM ON
SET TIMING OFF
SET HEAD ON
SET VERI OFF
SET FEED OFF
SET PAUSE OFF
SET PAGES 66
SET RECSEP OFF
SET LINES 132
SET ARRAYSIZE 5

BTITLE OFF
TTITLE OFF

CLEAR BREAKS
CLEAR COMPUTE
CLEAR COLUMNS
CLEAR SCREEN

COL l FOR A78 TRUNC

ACCEPT us_ CHAR PROMPT "Username (LIKE format - default= all): "

TTITLE CENTER "Locks by users (except type MR) by &&us_" SKIP -
RIGHT ""

COL username FOR A08 HEAD "USER OS" TRUNC
COL pid FOR 999 HEAD "PID" TRUNC
COL spid FOR A06 HEAD "SID" TRUNC
COL ora FOR A08 HEAD "USER ORA" TRUNC
COL lock FOR A10 HEAD "LOCKS" TRUNC
COL type FOR A27 HEAD "TYPE" WRAPPED
COL lmode FOR A04 HEAD "MODE" TRUNC
COL wait FOR A01 HEAD "W" TRUNC

BREAK ON username -
ON pid -
ON spid -
ON ora -
ON lock

-- SPOOL lockv7

SELECT p.username ,
p.pid ,
p.spid ,
s.username ora ,
DECODE(l2.type,
'TX','TRANSACTION ROW-LEVEL' ,
'RT','REDO-LOG' ,
'TS','TEMPORARY SEGMENT ' ,
'TD','TABLE LOCK' ,
'TM','ROW LOCK' ,
l2.type ) vlock,
DECODE(l2.type,
'TX','DML LOCK' ,
'RT','REDO LOG' ,
'TS','TEMPORARY SEGMENT' ,
'TD',DECODE(l2.lmode+l2.request ,
4,'PARSE ' ||
u.name ||
'.' ||
o.name ,
6,'DDL' ,
l2.lmode+l2.request),
'TM','DML ' ||
u.name ||
'.' ||
o.name ,
l2.type ) type ,
DECODE(l2.lmode+l2.request ,
2 ,'RS' ,
3 ,'RX' ,
4 ,'S' ,
5 ,'SRX' ,
6 ,'X' ,
l2.lmode+l2.request ) lmode ,
DECODE(l2.request ,
0,NULL ,
'WAIT' ) wait
FROM v$process p ,
v$_lock l1,
v$lock l2,
v$resource r ,
sys.obj$ o ,
sys.user$ u ,
v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
AND p.username LIKE NVL('&&us_','%')
ORDER BY
1,
2,
3,
4,
5
/
-- SPOOL OFF

Report message to a moderator

Re: Lock [message #38999 is a reply to message #38994]

Wed, 05 June 2002 12:32

andrew again
Messages: 2577
Registered: March 2000

Senior Member

You might like to try a tool like TOAD to make your like easier - it has all of that in the DBA module.

Report message to a moderator

Previous Topic:	Handling a table
Next Topic:	Slow dbms_alert.register('event')

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 09:56:15 CDT 2024