-Holders and Waiters for locks
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
Lock History
WITH ash_query AS (
SELECT event lock_type,program,
h.module, h.action, object_name, h.session_id, h.session_serial#, h.sample_time,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY event ,program, h.module, h.action,
object_name, h.session_id, h.session_serial#, h.sample_time, sql_text, username)
SELECT lock_type,module, username, object_name, session_id, session_serial#, sample_time, sql_text, time_ms,pct_of_time
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank
RAC Blocking Session
with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
from gv$session where blocking_instance is not null and blocking_session is not null)
select lpad(' ',2*(level-1))||waiter lock_tree from
(select * from lk
union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
set serverout on
declare
sess varchar2(20);
sessinfo varchar2(29);
begin
for i in
(with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
from gv$session where blocking_instance is not null and blocking_session is not null)
select distinct blocker from lk where blocker not in (select waiter from lk)
)
loop
select regexp_substr(i.blocker,'[0-9]+$')||','||serial# ||',@' || regexp_substr(i.blocker,'[0-9]+'),
substr(username||':'||program,1,29) into sess, sessinfo
from gv$session where inst_id = regexp_substr(i.blocker,'[0-9]+') and sid = regexp_substr(i.blocker,'[0-9]+$');
dbms_output.put_line(sessinfo || ' ' || 'alter system kill session ''' || sess || ''' immediate;');
end loop;
end;
select ( select s2.username || '@' || s2.machine from gv$session s2 where s2.sid=s1.blocking_session and s2.inst_id=s1.blocking_instance and s2.username is n
ot null) || ' ( SID=' || s1.blocking_session || ' from instance= '|| s1.blocking_instance ||' ) is blocking ' || s1.username || '@' || s1.machine ||' ( SID='||s1.s
id||' )' as blocking_status
from gv$session s1
where BLOCKING_SESSION is not null
and s1.username is not null;
