oracle锁查询常用脚本

with
  lockinfo as (
   select distinct decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) sql_hash_value, decode  (sql_hash_value, 0, prev_sql_addr, sql_address) sql_address, s.sid, l.id1 object_id, l.block
      from v$lock l, v$session s
     where l.sid = s.sid and s.type = 'USER' and decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) > 0)
select s.sid, s.serial#, p.spid ospid, (case when block>0 then 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' end) kill_sql,
       o.owner, o.object_name, o.object_type, s.username login_username, s.machine, s.client_info, s.osuser, s.terminal, s.module, s.program, s.status,
       aa.name command_type,
       -- s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#,
       decode(s.row_wait_obj#, -1, null, dbms_rowid.rowid_create(1, s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#, s.row_wait_row#)) row_wait_rowid,
       st.sql_text
  from lockinfo li, v$session s, dba_objects o, v$process p, audit_actions aa, v$sqltext st
 where li.sid = s.sid(+) and s.paddr = p.addr(+) and li.object_id = o.object_id and s.command = aa.action(+) and li.sql_address = st.address(+) and li.sql_hash_value = st.hash_value(+)
    -- and o.owner = upper('SCOTT') and o.object_name = upper('STATS_TABLE')
 order by li.block desc, s.row_wait_obj#, s.sid, li.sql_address, li.sql_hash_value, st.piece;
--阻塞脚本  查询谁阻塞谁
col block_msg for a80
select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid block_msg
from v$lock a,v$lock b,v$session c
where a.id1=b.id1
  and a.id2=b.id2
  and a.block>0
  and a.sid<>b.sid
  and a.sid=c.sid
--ORACLE 锁表查询
  SELECT /*+ rul