动态视图学习计划列表

V$TRANSACTION lists the active transactions in the system. When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.

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    }

用如下SQL查询到正在运行的事务，如其used_urec字段不断增加，说明该事物正在继续，如果该字段不断下降，说明该事物正在回滚。

SQL> 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
---------- ------------------------------ ---------- ---------- ----------
       118 CCP                                   102         12          1

SQL> 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;
  2    3 
       SID USERNAME                           XIDUSN  USED_UREC  USED_UBLK
---------- ------------------------------ ---------- ---------- ----------
       596 GCC                                    87          2          1