1. find blocked sessions and blocking session
SQL>
select l1.sid “blocking session”, l2.sid “blocked session”
from v$lock l1, v$lock l2
where l1.sid != l2.sid and l1.id1 = l2.id1 and l1.id2 = l2.id2 and
l2.request > 0 and l1.block = 1;
2. find how long the blocked session is waiting:
SQL>
select
blocking_session “blocking_session”,
sid “blocked_session”,
serial# “blocked_serial#”,
seconds_in_wait/60 “wait_time(min)”
from v$session
where blocking_session is not null
order by blocking_session;
3. find which SQL command is waiting
SQL>
select s.sid, s.serial# ser#, s.process os_id, s.status, sql.sql_fulltext
from v$session s, v$sql sql, v$process p
where
s.sql_id=sql.sql_id and
s.sql_hash_value=sql.hash_value and
s.paddr=p.addr and
s.sid=&enter_blocked_session_sid;
4. resolving locks
If the Blocking session do not issue COMMIT, ROLLBACK or disconnects from the database,
Then you can kill that session — After having the approval from the owner of the sql such application team if is involved user/app schema, DBA/Oracle Support if the sql is database internal).
SQL>
alter system kill session ‘SID,Serial#’;
