1、创建测试表:
create table test(id int,info varchar);
insert into test select 1,'a';
insert into test select 2,'b';
commit;
2、创建两个会话分别执行:
会话1:update test set info ='c' where id=1;
会话2:update test set info ='d' where id=1;
执行以下语句查询等待关系:
WITH A AS(SELECT SESS_ID AS WAITOR_SESSID,SQL_TEXT AS WAITOR_SQL,ID AS WAITOR,WAIT_FOR_ID,WAIT_TIME
FROM SYS."V$SESSIONS" V,V$TRXWAIT T
WHERE TRX_ID=ID),
B AS(SELECT SESS_ID AS WAIT_FOR_SESSID,SQL_TEXT AS WAIT_FOR_SQL,ID AS WAITOR
FROM SYS."V$SESSIONS" V,V$TRXWAIT T
WHERE TRX_ID=WAIT_FOR_ID)
SELECT A.*,B.WAIT_FOR_SESSID,B.WAIT_FOR_SQL
FROM A,B WHERE A.WAITOR=B.WAITOR;
结果: