Blocking Session

How to find blocking session and kill the session from database

During adpatch ,deadlock will happen when some jobs depends on resource where another job its holding the resource.Using adctrl ,you can find two jobs in running state there will be no log updates.
* To find blocking session jobs below query will useful. It will return two rows.

select process,sid, blocking_session from v$session where blocking_session is not null;
E.g.
SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
PROCESS SID BLOCKING_SESSION
———— ———- —————-
1234 365 366
1234 366 365

* Second step to find the serial number for the Blocking Session to kill
select SERIAL# from v$session where SID=<SID number>
E.g.
SQL> select SERIAL# from v$session where SID=365;
SERIAL#
———-
130

* Final step to kill the blocking session
alter system kill session ‘SID,SERIAL#’;
E.g.
SQL> alter system kill session ’365,130′;
System altered.




What's your database version? Please show us all 5 digits of the version (select from v$version);*

In 10.1_

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production

SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

no rows selected

In 10.2*

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

no rows selected

In 11g _(showing a real lock situation)_

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.


SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

       SID SQL_ID        USERNAME                       BLOCKING_SESSION
---------- ------------- ------------------------------ ----------------
       145 62ttrb9xf43zr SCOTT                                      26

1 row selected.

Find and resolve Blocking Session

In the life of Oracle/Oracle Apps DBA, we usually fall in the pit of blocking session. First we need to find whether any blocking session is happening by the following query:-

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

If there is any blocking session, we must check the "second_in_wait" as well as the sid which is blocking. If the session wait is higher and the its inactive for long time, then we can get rid of that session by:-

alter system kill session 'sid,serial#' immediate;
for eg: alter system kill session '1140,188' immediate;

Other Useful Queries related with Locks & sessions:-

SELECT o.object_name,
v.session_id sid,
v.oracle_username,
decode(l.type,'TM', 'DML enqueue (TM)', 'TX', 'Transaction enqueue (TX)', 'UL', 'User supplied (UL)', l.type),
decode(l.lmode,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.lmode,'990')) holding,
decode(l.request,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.request,'990')) wanting,
l.block,
s.terminal,
s.machine
FROM sys.v_$locked_object v,
sys.v_$lock l,
sys.dba_objects o,
sys.v_$session s
WHERE l.sid = v.session_id
AND s.sid = v.session_id
AND v.object_id = o.object_id;

**********************************************************
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.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(lmode)) mode_held, s.event
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
and l.sid = s.sid
and v.session_id = l.sid
and s.process = v.process
order by oracle_username
, session_id


select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));



***************************************
select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event#
-- and SESSION_ID=213 and SAMPLE_TIME>=(sysdate-&minute/(24*60));

CAN ALSO USE THE BELOW

select do.object_name,sid,s.serial#,s.osuser,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, --s.session_id,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID ;