SELECT ( '节点 '|| a.inst_id|| ' session '|| a.sid|| ','|| a_s.serial#|| ' 阻塞了 节点 '|| b.inst_id|| ' session '|| b.sid|| ','|| b_s.serial#)
blockinfo, a.inst_id, a_s.sid, 'alter system kill session '||''''|| a.sid||','||a_s.serial#||''''||';' as KillCommand,
a_s.schemaname, a_s.module, a_s.status, a.TYPE lock_type, a.id1,a.id2, DECODE (a.lmode, 0, 'none', 1, NULL, 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)', 6, 'exclusive (X)') lock_mode,'后为被阻塞信息' remark_flag,b.inst_id blocked_inst_id,b_s.sid blocked_sid,b.TYPE blocked_lock_type,DECODE (b.request, 0, 'none', 1, NULL, 2, 'row-S (SS)', 3, 'row-X (SX)', 4, 'share (S)', 5, 'S/Row-X (SSX)',
6, 'exclusive (X)') blocked_lock_request,b_s.schemaname blocked_schemaname,
b_s.module blocked_module,b_s.status blocked_status,b_s.sql_id blocked_sql_id, obj.owner blocked_owner,
obj.object_name blocked_object_name,obj.object_type blocked_object_type,
CASE WHEN b_s.row_wait_obj# <> -1 THEN DBMS_ROWID.rowid_create (1, obj.data_object_id, b_s.row_wait_file#, b_s.row_wait_block#, b_s.row_wait_row#)
ELSE '-1' END blocked_rowid, --被阻塞数据的rowid
DECODE (obj.object_type, 'TABLE', 'select * from ' || obj.owner || '.' || obj.object_name
|| ' where rowid=''' || DBMS_ROWID.rowid_create (1, obj.data_object_id, b_s.row_wait_file#, b_s.row_wait_block#,
b_s.row_wait_row#) || '''', NULL) blocked_data_querysql FROM gv$lock a, gv$lock b, gv$session a_s, gv$session b_s,
dba_objects obj WHERE a.id1 = b.id1 AND a.id2 = b.id2 AND a.block > 0 --阻塞了其他人
AND b.request > 0 AND ( (a.inst_id = b.inst_id AND a.sid <> b.sid) OR (a.inst_id <> b.inst_id)) AND a.sid = a_s.sid AND a.inst_id = a_s.inst_id AND b.sid = b_s.sid
AND b.inst_id = b_s.inst_id AND b_s.row_wait_obj# = obj.object_id(+)ORDER BY a.inst_id, a.sid;
oracle检查数据库是否有锁
最新推荐文章于 2024-09-13 17:09:00 发布