with vw_lock AS (SELECT * FROM v$lock)
select
a.sid, 'is blocking',
(select 'sid:'||s.sid||' object:' ||do.object_name||' rowid:' ||
dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLoCK#, ROW_WAIT_ROW# )||'sql_id:'|| s.sql_id
from v$session s, dba_objects do
where s.sid=b.sid
and s.ROW_WAIT_OBJ# = do.OBJECT_ID
) blockee,
b.sid,b.id1,b.id2
from vw_lock a, vw_lock b
where a.block = 1
and b.request>0
and a.id1=b.id1
and a.id2 = b.id2
select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machine
from v$process a, v$session b, v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
select name, value from v$sesstat s, v$statname n where n.statistic# = s.statistic# and upper(name) like upper('%redo size%') and s.sid=50;
查找oracle锁以及阻塞、查找会话产生的redo
最新推荐文章于 2021-11-08 15:59:24 发布