/* Use the below query to check Locked Sessions and Concurrent Request */
SQL>select
b.process " Process id ",
b.osuser,
b.machine,
b.sid,
b.serial#,
b.schemaname,
b.module,
b.action,
trunc(b.SECONDS_IN_WAIT/60/60,3) " Waiting Time in H ",
blocking_session,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null *** -- alter system kill session '''||b.sid||','||b.SERIAL#||''' immediate;' " SQL kill session statement" ,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null --- kill -9 '||b.process " OS kill process command",
b.PADDR,
p.SPID,
b.PROCESS
from v$session b , v$process p
where b.PADDR=p.ADDR and b.sid in (select BLOCKING_SESSION from v$session s where BLOCKING_SESSION is not null);
*********************************************************************************
SQL>select
b.process " Process id ",
b.osuser,
b.machine,
b.sid,
b.serial#,
b.schemaname,
b.module,
b.action,
trunc(b.SECONDS_IN_WAIT/60/60,3) " Waiting Time in H ",
blocking_session,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null *** -- alter system kill session '''||b.sid||','||b.SERIAL#||''' immediate;' " SQL kill session statement" ,
'*** KILL ONLY IF BLOCKING_SESSION IS NULL, otherwise trace that blocking session till has null --- kill -9 '||b.process " OS kill process command",
b.PADDR,
p.SPID,
b.PROCESS
from v$session b , v$process p
where b.PADDR=p.ADDR and b.sid in (select BLOCKING_SESSION from v$session s where BLOCKING_SESSION is not null);
*********************************************************************************
Does this query shows which concurrent request is blocking session?
ReplyDelete