Oracle 阻塞

確認存在被阻塞的會話

select * from dba_blockers;

確認哪個會話阻塞了其他會話

select l1.sid, ‘ IS BLOCKING ‘, l2.sid

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2

----------------------------------------------

select s1.username || ‘@’ || s1.machine

|| ‘ ( SID=’ || s1.sid || ‘ )  is blocking ‘

|| s2.username || ‘@’|| s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status

from v$lock l1, v$session s1, v$lock l2, v$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 ;

——————————————————

ID2為對應的object id

select * from v$lock ;

select object_name from dba_objects where object_id=307193;

查詢阻塞其他會話對應的行。

select do.object_name,

row_wait_obj#, do.data_object_id, row_wait_file#, row_wait_block#, row_wait_row#,

dbms_rowid.rowid_create ( 1, do.data_object_id, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

from v$session s, dba_objects do

where sid=252

and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

select * from obj1 where rowid=’ AABK/5AAFAAAAHHAAA’ ;

————————————————————

查詢阻塞其他會話的會話正在執行的SQL

select s.sid, q.sql_text from v$sqltext q, v$session s

where q.address = s.sql_address

and s.sid = 252;

kill 會話

Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by blocking_session;

alter system kill session 244,11049′ immediate;