• Is the transaction moving forward or rolling back?
• Is it possible to monitor the rollback activity?
• Is the transaction hung or is it performing work?
• How long will the current transaction take to rollback?

The details needed to answer these questions can be obtained from the v$transaction dynamic performance view.

Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.

Additionally, starting with Oracle V7.3, some columns were added to v$transaction view that will allow the DBA to monitor a transaction in greater detail.

If the database has not been shutdown and restarted, look at look at v$transaction.used_urec and v$transaction.used_ublk. These fields are the number of undo records and undo blocks currently held by a transaction.

By querying v$transaction over a time interval, the number of records/blocks rolled back in a given time period can be calculated from this rate. Use the following to figure how long it will take to rollback the entire transaction.

v$session can be joined to v$transaction using the following query:

  SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
  FROM v$session a, v$transaction b
  WHERE a.saddr = b.ses_addr;

         SID     USERNAME     XIDUSN  USED_UREC  USED_UBLK
  ---------- ------------ ---------- ---------- ----------
          18   TRUESOURCE          4      10000         82

The above shows that sid 18 is holding 10,000 undo records in rollback segment number 4.

If the database HAS been shutdown and restarted.

If the database has been shutdown (abort) and restarted, the information in v$transaction is reset and is not useful. To find out how long the rollback will take, dump the rollback segment header to find the number of undo blocks. Take two segment header dumps, calculate the number of undo blocks rolled back during the time interval, and then calculate how long to roll back the entire transaction.

If the database has been restarted it will be difficult to tell which rollback segment was being used so you will need to dump all the rollback segment headers initially.

To dump the file headers, first determine which block stores the file header.

  SELECT segment_name, header_file, header_block
  FROM dba_segments
  WHERE segment_type='ROLLBACK';

  SEGMENT_NAME    HEADER_FILE HEADER_BLOCK
  --------------- ----------- ------------
  SYSTEM                    1            2
  RBS0                      2            2
  RBS1                      2          514
  RBS2                      2         1026
  RBS3                      2         1538

Next, issue the following command in 8.x+ to dump the file header.

alter system dump datafile 2 block 1026;

This will generate a dump file in user_dump_dest. In the dump file look for the transaction table for the rollback segment. There will be a column called nub which holds the number of undo blocks for the transaction.

  -- Trace file snippet follows:

  index  state cflags  wrap#    uel         scn            dba            parent-xid    nub
  ------------------------------------------------------------------------------------------------
   0x00   10    0xc0  0x1995  0x0007  0x0000.009dd0ac  0x00800193  0x0000.000.00000000  0x00000052
   0x01    9    0x80  0x1994  0x003c  0x0000.009dd007  0x00801f3f  0x0000.000.00000000  0x00000001

Notice that the first slot holds an uncommitted transaction (state=10) and the nub (number undo blocks) = 0x52 or decimal 82.

If the database has been restarted in 9i, there is an easier way to determine the number of undo blocks required for rollback by using the following query:

  SELECT DISTINCT ktuxesiz FROM x$ktuxe;

    KTUXESIZ
  ----------
           0
           1
           2
           3
         107

Earlier versions of Oracle may not include all columns.

The v$transaction view lists the active transactions in the system.

  Column        Datatype       Description
  ------------- -------------- --------------------------------------
  ADDR          RAW(4)         Address of transaction state object 
  XIDUSN        NUMBER         Undo segment number 
  XIDSLOT       NUMBER         Slot number 
  XIDSQN        NUMBER         Sequence number 
  UBAFIL        NUMBER         Undo block address (UBA) filenum 
  UBABLK        NUMBER         UBA block number 
  UBASQN        NUMBER         UBA sequence number 
  UBAREC        NUMBER         UBA record number 
  STATUS        VARCHAR2(16)   Status 
  START_TIME    VARCHAR2(20)   Start time (wall clock) 
  START_SCNB    NUMBER         Start system change number (SCN) base 
  START_SCNW    NUMBER         Start SCN wrap 
  START_UEXT    NUMBER         Start extent number 
  START_UBAFIL  NUMBER         Start UBA file number 
  START_UBABLK  NUMBER         Start UBA block number 
  START_UBASQN  NUMBER         Start UBA sequence number 
  START_UBAREC  NUMBER         Start UBA record number 
  SES_ADDR      RAW(4)         User session object address 
  FLAG          NUMBER         Flag 
  SPACE         VARCHAR2(3)    YES if a space transaction 
  RECURSIVE     VARCHAR2(3)    YES if a recursive transaction 
  NOUNDO        VARCHAR2(3)    YES if a no undo transaction 
  PTX           VARCHAR 2(3)   YES if parallel transaction 
  NAME          VARCHAR2(256)  Name of a named transaction 
  PRV_XIDUSN    NUMBER         Previous transaction undo segment number 
  PRV_XIDSLT    NUMBER         Previous transaction slot number 
  PRV_XIDSQN    NUMBER         Previous transaction sequence number 
  PTX_XIDUSN    NUMBER         Rollback segment number of the parent XID  
  PTX_XIDSLT    NUMBER         Slot number of the parent XID 
  PTX_XIDSQN    NUMBER         Sequence number of the parent XID 
  DSCN-B        NUMBER         Dependent SCN base 
  DSCN-W        NUMBER         Dependent SCN wrap 
  USED_UBLK     NUMBER         Number of undo blocks used 
  USED_UREC     NUMBER         Number of undo records used 
  LOG_IO        NUMBER         Logical I/O 
  PHY_IO        NUMBER         Physical I/O 
  CR_GET        NUMBER         Consistent gets 
  CR_CHANGE     NUMBER         Consistent changes 

  Comments on a few of the columns:
  --------------------------------
  XIDUSN          Rollback Segment ID       }  Transaction ID is
  XIDSLOT         Slot in RBS TX table      }   USN.SLOT.SQN or
  XIDSQN          Wrap of the entry         }  TX-USNxSLOT-SQNxxxxx

  UBAFIL          File for last undo entry  }  Tail end of UNDO for
  UBABLK          Block for last undo entry }  this transaction
  UBASQN          Sequence no of last entry }
  UBAREC          Record no in the block    }