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 /*+ rule */
       s.username,
       DECODE (l.TYPE,
               'TM', 'TABLE LOCK',
               'TX', 'ROW LOCK',
               NULL
              ) lock_level, o.owner, o.object_name, o.object_type, s.SID,
       s.serial#, s.terminal, s.machine, s.program, s.osuser
  FROM v$session s, v$lock l, dba_objects o
 WHERE l.SID = s.SID AND l.id1 = o.object_id(+) AND s.username IS NOT NULL;

 

 

--查锁脚本
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", a.username, a.machine, a.sid, a.serial#,
a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null
and a.lockwait = b.kaddr
and c.hash_value =a.sql_hash_value
union
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", a.username, a.machine, a.sid, a.serial#,
a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where b.id1 in
(select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1
from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.username is not null
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;