常用sql语句-查阻塞、不活动会话

查询死锁堵塞的会话sid

select * from V$SESSION_BLOCKERS 
select * from dba_waiters

select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话STATUS持续时间_S" 
from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID' 

把上面被堵塞会话的sid代入如下语句，可以发现锁住的对象和对象的哪一行(如果sid是堵塞源的会话，则 row_wait_obj#=-1，表示锁持有者，就是死锁源了 )

select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# 
from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid  in(XX,XX) 

--查询超过两个小时的不活动会话

select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p  
where  s.paddr=p.addr and s.sid in (select sid from v$session where  status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200);

===============================================

--查询堵塞别的会话超过30分钟且自身是不活动的会话

select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session
 where sid in (select FINAL_BLOCKING_SESSION from v$session
  where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' and sql_id is null and seconds_in_wait>1800