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
