Blocking sessions
                                                 Last update (2011-04-08 09:49:32)
                                                                                                                   Date added (2008-02-14 13:50:48)

Summary
The script following finds the blocking sessions in the database
SELECT /*+ RULE */ 
 LPAD('--->',DECODE(A.request,0,0,5))||A.SID SID, B.serial#,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || 
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, 
b.status, b.SCHEMANAME SCHEMA, 
DECODE(o.owner || '.' || o.object_name, '.', NULL, o.owner || '.' || o.object_name) OBJECT, o.object_type,
 b.osuser, b.machine, b.module, b.program, 
 DECODE(BLOCK, 0, NULL, 'BLOKER' ) || DECODE(request, 0, NULL, '-->WAITER' ) BLOKER,
 DECODE (A.lmode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 
'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', A.lmode) lmode,
DECODE(TRUNC(ctime/86400), 0, TO_CHAR(TO_DATE(ctime, 'SSSSS'), 'HH24:MI:SS'), 
TRUNC(ctime/86400) || ' Days + ' || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime/86400))*86400 , 'SSSSS'), 'HH24:MI:SS')) TIME, 
A.TYPE, 'alter system kill session ' || '''' || a.SID || ', ' || b.serial# ||'''' || ' immediate;' kill_session, 
DECODE(object_type, NULL, NULL, 'Dbms_Rowid.rowid_create(1, ' || row_wait_obj# || ', ' 
|| row_wait_file# ||', ' || row_wait_block#||', ' || row_wait_row# ||')') row_id 
 FROM v$lock A, v$session b, dba_objects o
 WHERE A.SID = b.SID 
  AND (lmode = 0 OR BLOCK = 1) 
  AND o.object_id (+) = DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#)
 ORDER BY A.id1,A.request;
Tip: The last column of the query for blocked sessions is ROWID of the record!!!!
To find the exact locked row execute:
SELECT * FROM OBJECT 
WHERE ROWID = Dbms_Rowid.rowid_create(1, 48804, 31, 114809, 210);
Reviews
Categories
Filters
Search