查詢等待的session

使用$ORACLE_HOME/rdbms/admin下的指令碼。

-- 查詢等待的session
a1. $ORACLE_HOME/rdbms/admin
a2. startup svrmgrl, connect internal
a3. run catblock.sql
a4. run utllockt.sql
Sample Output:  WAITING_ LOCK_TYPE         MODE_REQUE MODE_HELD  LOCK_ID1   LOCK_ID2
-------- ----------------- ---------- ---------- ---------- ----------
                 5        None   
      6     Transaction       Exclusive  Exclusive  262172     90   
      9     Transaction       Exclusive  Exclusive  262172     90 
-- Sessions 6 and 9 are waiting on session 5. 
a5. select sid,serial# from v$session where sid = 5;
a6. alter system kill session 'sid,serial#';    

-- 以下查詢檢視session正在執行的sql
select t1.username,t1.status,t2.sql_text
from v$session t1,v$sqlarea t2
where t1.sql_hash_value = t2.hash_value
and t1.username is not null and t1.sid = &sid
/