// *******************************************************************************-- blocking issue : enq: TX - row lock contention
// *******************************************************************************
step 1
// get blocking session and blocked sessions
set lines 180
col USERNAME format a10
col event format a15
col WAIT_CLASS format a15
Select blocking_session, sid, serial#, username, event, wait_class, seconds_in_wait siw
from v$session where blocking_session is not NULL order by blocking_session;
step 2
// detail locking information : sid, serial and object name
set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
order by 2,3;
step 3
// which sid and stmt is waiting for lock
with blocking_tab as (
Select distinct blocking_session sid
from v$session where blocking_session is not NULL
),
blocking_sess as (
select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.sid=b.sid
),
blocked_sess as (
select s.sid, s.serial#, sql_id, prev_sql_id from v$session s, blocking_tab b where s.blocking_session=b.sid ),
wait_sess as (
select sid, serial#, sql_id, prev_sql_id from blocking_sess union select sid, serial#, sql_id, prev_SQL_id from blocked_sess )
select s.sid, s.serial#, sql_text from v$sql q, wait_sess s where
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id
/
step 4
// kill blocking session
with temptbl1 as (
Select distinct blocking_session sid
from v$session where blocking_session is not NULL
)
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';' from v$session natural join temptbl1 ;
// *******************************************************************************
