oracle杀死锁脚本,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