oracle怎么查阻塞,oracle 查询阻塞的sql语句

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

session1；

create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;

alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);

insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');

insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');

commit;

update t_all_objs set object_name='test11' where object_id=2013011701;

select sid from v$mystat where rownum=1;--157

session 2

SQL> select sid from v$mystat where rownum=1;

SID

----------

273

SQL> update stz.t_all_objs set object_name='test101' where object_id=2013011701;

会一直等待.

session 3

SQL> select sid from v$mystat where rownum=1;

SID

----------

419

SQL> update stz.t_all_objs set object_name='test101419' where object_id=2013011701;

也会一直等待。

session4；

SELECT 'blocker('

|| wb.holding_session

|| ':'

|| sb.username

|| ')-sql:'

|| qb.sql_text

blockers,

'waiter ('

|| wb.waiting_session

|| ':'

|| sw.username

|| ')-sql:'

|| qw.sql_text

waiters

FROM dba_waiters wb,

v$session sb,

v$session sw,

v$sqlarea qb,

v$sqlarea qw

WHERE wb.holding_session = sb.sid

AND wb.waiting_session = sw.sid

AND sb.prev_sql_addr = qb.address

AND sw.sql_address = qw.address

AND wb.mode_held <> 'None';

blocker(157:stz)-sql:select sid from v$mystat where rownum=1 waiter (419:SYS)-sql: update stz.t_all_objs set object_name='test101419' where object_id=2013011701

blocker(157:stz)-sql:select sid from v$mystat where rownum=1 waiter (273:SYS)-sql:update stz.t_all_objs set object_name='test101' where object_id=2013011701

现在只能查询到 sid 为157的session的sql 为：执行完update没有提交之后 执行的sql语句select sid from v$mystat where rownum=1。可以通过什么方式查询到 sid为157 执行的 未提叫的sql语句(update t_all_objs set object_name='test11' where object_id=2013011701)呢？

谢谢。