dba_script - DB locks/Blocks/Blocker
ÀÛ¼ºÀÚ °ü¸®ÀÚ ÀÛ¼º½Ã°£ 2016-02-22 16:59:23
 

1. Track Block session in oracle 9i/10g 
‎select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||  ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||  s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid = l1.sid and s2.sid = l2.sid  and l1.BLOCK = 1  and l2.request > 0  and l1.id1 = l2.id1  and l2.id2 = l2.id2;

select do.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create(1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from gv$session s, dba_objects do
where sid = 543 and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

2. For detail description of blocking you can run this on your Oracle-Home
oracle-home\rdbms\admin\utllockt.sql

3. Track Locked Session & Blocked:
PROMPT Blocked and Blocker Sessions
select /*+ ORDERED */ blocker.sid blocker_sid, blocked.sid blocked_sid ,
TRUNC(blocked.ctime/60) min_blocked, blocked.request
from (select *from v$lock
where block != 0 and type = 'TX') blocker, v$lock blocked
where blocked.type='TX' and blocked.block = 0 and blocked.id1 = blocker.id1;

4. Track Database Lock:
Select /*+ ORDERED */ l.sid, l.lmode,
TRUNC(l.ctime/60) min_blocked, u.name||'.'||o.NAME blocked_obj
from (select * from v$lock
where type='TM' and sid in (select sid
from v$lock where block!=0)) l, sys.obj$ o, sys.user$ u
where o.obj# = l.ID1 and o.OWNER# = u.user#;

5. Track the Session Waiting for Lock:
SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type TYPE, mode_held, mode_requested, lock_id1, lock_id2
FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.holding_session = b.sid AND c.waiting_session = a.sid;

6. Track Blocker Details:
SELECT sid, serial#, username, osuser, machine
FROM v$session
WHERE sid IN (select sid from v$lock
where block != 0 and type = 'TX');


¸ñ·Ï | ÀÔ·Â | ¼öÁ¤ | ´äº¯ | »èÁ¦