檢視鎖定的session資訊指令碼

                   檢視當前被阻塞的物件和鎖資訊
SELECT DISTINCT
       s1.inst_id BlockingInst,
       s1.sid BlockingSid,
       s1.serial# BlockingSerial,
       s2.inst_id BlockedInst,
       s2.sid BlockedSid,
       s2.username BlockedUser,
       s2.seconds_in_wait BlockedWaitTime
FROM   gv$session s1,
       gv$lock l1,
       gv$session s2,
       gv$lock l2
WHERE  s1.inst_id = l1.inst_id and
       l1.block in (1, 2) and
       l2.request != 0 and
       l1.sid = s1.sid and
       l1.id1 = l2.id1 and
       l1.id2 = l2.id2 and
       s2.sid = l2.sid and
       s2.inst_id = l2.inst_id
ORDER BY 1, 2, 3


檢視被鎖的回話資訊：
SELECT
   s.blocking_session, 
   s.sid, 
   s.osuser,
   s.seconds_in_wait,
   s.event,
   s.state,
   t.sql_text,
   o.object_name
FROM
   v$session s, v$sqltext t, v$locked_object lo, dba_objects o
WHERE
   blocking_session IS NOT NULL
   and t.hash_value=s.sql_hash_value
   and s.sid=lo.session_id
   and lo.object_id=o.object_id
ORDER BY t.hash_value,t.PIECE;

以DBA角色, 檢視當前資料庫裡鎖的情況可以用如下SQL語句：

col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
/

select t2.username,t2.sid,t2.serial#,t2.logon_time ,t1.object_id,t3.object_name,t1.locked_mode

from v$locked_object t1,v$session t2 ,dba_objects t3

where t1.session_id=t2.sid and t1.object_id=t3.object_id order by t2.logon_time

/

如果有長期出現的一列，可能是沒有釋放的鎖。我們可以用下面SQL語句殺掉長期沒有釋放非正常的鎖：

alter system kill session 'sid,serial#';


另外幾個可以查詢阻塞回話的檢視：
DBA_WAITERS
DBA_BLOCKERS
V$WAIT_CHAINS