--数据库等待大于100的死锁
SELECT l.CTIME lock_totaltime,
s.username sesseion_user,
s.logon_time sesseion_time,
s.type sesession_type,
s.process session_unixid,
s.LOCKWAIT session_lockaddress,
s.BLOCKING_SESSION_STATUS session_blockstatus,
s.STATE session_waittime,
s.sid session_SID,
s.serial# session_id,
s.sql_id sesseion_sqlid,
decode(s.STATUS,
'ACTIVE',
'正在执行',
'INACTIVE',
'会话失效',
'KILLED',
'杀掉的',
'CACHED',
'临时的',
'SNIPED',
'失效正待客户端') session_status,
s.terminal session_terminalname,
s.machine session_machine,
s.program session_program,
s.osuser session_OSuser,
s.MODULE session_modulename,
s.ACTION session_action,
decode(l.BLOCK, 0, '没有阻塞', 1, '阻塞', 2, '全局锁无法kill') lock_blockstatus,
decode(l.lmode,
0,
'None',
1,
'null',
2,
'SS行级共享锁',
3,
'SX',
4,
'S',
5,
'SSX',
6,
'EX') lock_mode,
l.type lock_type,
l.REQUEST lock_request,
o.object_type lock_object,
o.owner || '.' || o.object_name lock_holdobject,
q.SQL_TEXT SQL_text,
q.EXECUTIONS SQL_execount,
q.BUFFER_GETS SQL_memory,
q.DISK_READS SQL_IO,
round(q.ELAPSED_TIME / 1000000, 1) elapsedtime,
round(q.CPU_TIME / 1000000, 1) SQL_cputime,
round(q.APPLICATION_WAIT_TIME / 1000000, 1) app_wait_time,
round(q.CONCURRENCY_WAIT_TIME / 1000000, 1) binfa_wait_time,
round(q.USER_IO_WAIT_TIME / 1000000, 1) userio_wait_time,
round(q.PLSQL_EXEC_TIME / 1000000, 1) plsql_exec_time,
round(q.JAVA_EXEC_TIME / 1000000, 1) java_exec_time,
q.FIRST_LOAD_TIME SQL_fisttime,
rpad(lo.oracle_username, 10) lockobject_user,
lo.session_id lockobject_SID,
decode(lo.locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lockobject_type
FROM sys.v_$session s,
sys.v_$lock l,
sys.v_$locked_object lo,
sys.dba_objects o,
sys.v_$sql q
WHERE s.sid = l.sid
and s.sid = lo.SESSION_ID
and lo.OBJECT_ID = o.OBJECT_ID(+)
and s.SQL_HASH_VALUE = q.HASH_VALUE(+)
and s.SQL_ID = q.SQL_ID(+)
AND s.username is NOT NULL
-- and s.username in ('TELCHANGE')
AND l.CTIME > 100
and round(q.APPLICATION_WAIT_TIME / 1000000, 1) > 5000
and to_char(s.logon_time, 'HH24:mi') >= '07:00'
and to_char(s.logon_time, 'HH24:mi') <= '22:00'