查看事务使用的undo

USED_UBLK NUMBER Number of undo blocks used
USED_UREC NUMBER Number of undo records used

select /*+ rule */s.sid,
     r.name rr,
       nvl(s.username,'no transaction') us,
       s.osuser os,
       s.terminal te,
       t.used_urec rec,
       t.used_ublk blk
  from v$lock l, v$session s, v$rollname r,v$transaction t
 where l.sid = s.sid(+) and
       trunc(l.id1/65536) = r.usn and
       l.type = 'TX' and
       t.ses_addr = s.saddr and
       l.lmode = 6;
       

查看被锁对象

SELECT L.*,O.OBJECT_NAME,
      S.SID,
      S.SERIAL#,
      P.SPID,
      S.PROGRAM,
      S.USERNAME,
      S.MACHINE,
      S.PORT,
      S.LOGON_TIME,
      SQ.SQL_FULLTEXT,
      s.sql_id
 FROM V$LOCKED_OBJECT L,
      DBA_OBJECTS O,
      V$SESSION S,
      V$PROCESS P,
      V$SQL SQ
WHERE     L.OBJECT_ID = O.OBJECT_ID
      AND L.SESSION_ID = S.SID
      AND S.PADDR = P.ADDR
      AND S.SQL_ADDRESS = SQ.ADDRESS and O.object_name='tableA';       
      

预估大事务回滚完成时间

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back ...
prompt

declare
  cursor tx is
    select
      s.username,
      t.xidusn,
      t.xidslot,
      t.xidsqn,
      x.ktuxesiz
    from
      sys.x$ktuxe  x,
      sys.v_$transaction  t,
      sys.v_$session  s
    where
      x.inst_id = userenv('Instance') and
      x.ktuxesta = 'ACTIVE' and
      x.ktuxesiz > 1 and
      t.xidusn = x.ktuxeusn and
      t.xidslot = x.ktuxeslt and
      t.xidsqn = x.ktuxesqn and
      s.saddr = t.ses_addr;
  user_name  varchar2(30);
  xid_usn    number;
  xid_slot   number;
  xid_sqn    number;
  used_ublk1 number;
  used_ublk2 number;
begin
  open tx;
  loop
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    then
      sys.dbms_lock.sleep(10);
    end if;
    select
      sum(ktuxesiz)
    into
      used_ublk2
    from
      sys.x$ktuxe
    where
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
    then
      sys.dbms_output.put_line(
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
        to_char(
          sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
          'HH24:MI:SS DD-MON-YYYY'
        )
      );
    end if;
  end loop;
  if user_name is null
  then
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;
end;
/