Situatie

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#’;

Solutie

Tip solutie

Permanent