如何查到未提交的事务，堵塞的记录

   正式环境上经常有SQL执行很慢，看执行计划没有问题，在测试环境上执行很快，可能是程序的业务逻辑写的有问题，唯有查下是否被堵塞。

   1、ROW_WAIT_OBJ#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,ROW_WAIT_FILE#的数值只在被阻塞的会话是有效的

   2、如果更新多行，则ROW_WAIT_OBJ#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,ROW_WAIT_FILE#对应的是第一个被阻塞的行的信息

select b.sid,

      b.serial#,

      b.process,

      d.ctime "LOCK TIME (sec)",

      c.object_name,

      decode(a.locked_mode,

             1, 'NULL',

               2,'ROW SHARED',

             3,'ROW EXCLUSIVE',

             4,'SHARED',

             5,'S/ROW EXCLUSIVE',

             6, 'EXCLUSIVE') "LOCK MODE",

      b.last_call_et "INACTIVE FOR(sec)",

      decode(d.block, 1, 'Yes', 0, 'No') "BLOCKING",

      dbms_rowid.rowid_create(1,

                              c.data_object_id,

                              b.ROW_WAIT_FILE#,

                              b.ROW_WAIT_BLOCK#,

                              b.ROW_WAIT_ROW#) "LOCKed ROWID"

 from v$locked_object a, v$session b, dba_objects c, v$lock d

 where b.sid = a.session_id

  and c.object_id = a.object_id

  and d.sid = a.session_id

  and d.id1 = a.object_id;

        实验：在session1中：update pub_user_bak u set u.cas_code='aaa' where  user_id='N00268';

   在session2中：update pub_user_bak u set u.cas_code='aaa' where  user_id='N00268';

    用上述SQL可以查出堵塞的rowid是：AAAYayAAJAAApsUAAF和AAAYayAAJAAAGcbAAA

    select * from pub_user_bak where rowid='AAAYayAAJAAApsUAAF';--这个可以查到，就是'N00268'

   select * from pub_user_bak where rowid='AAAYayAAJAAAGcbAAA';--这个查不到