SET LINESIZE 230
SET PAGESIZE 300
SET VERIFY OFF
COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a, gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4; Sample output:
Script which will generate the alter session kill command:select blocking_session,blocked_session,script from
( select distinct
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null)
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;
Finding the SQL_id with other details about the blocking sessions.
set lines 1234 pages 9999
col inst_id for a10
col serial# for a10
col machine for a30
col username for a10
col event for a20
col blocking_session for 999999
col blocking_instance for 999999
col status for a10
col INST_ID for 9999
col SERIAL# for 999999
select inst_id,sid,serial#, machine, username, event, blocking_session, blocking_instance, status, sql_id
from gv$session where status ='ACTIVE'and username is not null and username!='SYS';