oracle查询block会话,oracle查询v$lock锁里面block和被block的sql_text

首先在三个窗口执行SQL，依次执行，后一个窗口涉及到前一个窗口中修改的表

然后执行查询语句

---被阻塞事务的SQL

SELECT DISTINCT B.SID,

B.SERIAL#,

B.USERNAME,

B.MACHINE,

B.OSUSER,

B.PROGRAM,

A.MODULE,

TO_CHAR(B.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss'),

B.EVENT,

A.SQL_ID,

A.SQL_TEXT,

DECODE(C.REQUEST, 0, '', 'BLOCKED') BLOCKED,

DECODE(C.BLOCK, 0, '', 'BLOCKING') BLOCKING,

'alter system kill session ' || '''' || B.SID || ',' || B.SERIAL# || '''' || ';' AS FIX_SQL

FROM V$SQL A, V$SESSION B, V$LOCK C

WHERE C.TYPE = 'TX'

AND C.REQUEST <> 0

AND A.SQL_ID = B.SQL_ID

AND B.SID = C.SID

UNION ALL

--未提交或回滚的事务，有可能阻塞其他事务的SQL

SELECT DISTINCT ST.SID,

ST.SERIAL#,

ST.USERNAME,

ST.MACHINE,

ST.OSUSER,

ST.PROGRAM,

ST.MODULE,

TO_CHAR(ST.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss'),

ST.EVENT,

Q.SQL_ID,

Q.SQL_TEXT,

DECODE(L.REQUEST, 0, '', 'BLOCKED') BLOCKED,

DECODE(l.BLOCK, 0, '', 'BLOCKING') BLOCKING,

'alter system kill session ' || '''' || st.SID || ',' || SERIAL# || '''' || ';' AS FIX_SQL

FROM V$SQL Q,

v$lock l,

(SELECT S.SID,

S.SERIAL#,

S.USERNAME,

S.MACHINE,

S.OSUSER,

S.PROGRAM,

S.MODULE,

S.EVENT,

S.LOGON_TIME,

T.START_DATE

FROM V$SESSION S, V$TRANSACTION T

WHERE S.SADDR = T.SES_ADDR) ST

WHERE ST.START_DATE = Q.LAST_ACTIVE_TIME

and l.TYPE = 'TX'

and l.sid = st.sid

and l.block <> 0

AND REGEXP_LIKE(LOWER(SQL_TEXT),

(SELECT LISTAGG((LOWER(ALL_OBJECTS.OBJECT_NAME)), '|') WITHIN GROUP(ORDER BY OBJECT_NAME)

FROM V$LOCKED_OBJECT, ALL_OBJECTS

WHERE V$LOCKED_OBJECT.OBJECT_ID = ALL_OBJECTS.OBJECT_ID));

可以看到被block的SQL和block其他事务的事务的SQL