Oracle 查锁

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,
	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,
	'后为被阻塞信息',
	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

非原创，从评论里看到的，搬到这里是做个记忆，