10.144 V$TRANSACTION_TABLE

V$TRANSACTION_TABLE displays all transactions that are reflected on the undo segments in the database.

Column Datatype Description

XIDUSN

NUMBER

Undo segment number

XIDSLT

NUMBER

Slot number

XIDSQN

NUMBER

Sequence number

UBARFIL

NUMBER

Undo segment's relative file number

UBABLK

NUMBER

Undo segment's block number

TXNSCN

NUMBER

Transaction SCN

  • If the transaction is in the ACTIVE state, the start SCN

  • If the transaction is in the COMMITTED state, the commit SCN

  • If the transaction is in the PREPARED state, the prepare SCN (applies only to distributed or PDML transactions)

STATUS

VARCHAR2(16)

State of the transaction

  • ACTIVE

  • COLLECTING

  • COMMITTED

  • HEUR_ABORT

  • HEUR_COMMIT

  • HEUR_DAMAGE

  • IDLE

  • INACTIVE

  • NONE

  • PREPARED

  • TIMEOUT

COLLECTING

VARCHAR2(3)

Indicates whether the transaction has started storing collecting information (YES) or not (NO)

WRITE_COLLECTING_INFO

VARCHAR2(3)

Indicates whether the transaction has started writing the collecting information (YES) or not (NO)

PREPARED_DISTRIBUTED_RECOVERY

VARCHAR2(3)

For prepared transactions, indicates whether the transaction requires distributed recovery (YES) or not (NO)

DEAD

VARCHAR2(3)

Indicates whether rollback failed on the transaction and the transaction is marked for SMON to recover (YES) or not (NO)

PREPARED_EXTERNAL_ROLLBACKED

VARCHAR2(3)

For prepared transactions, indicates whether the externally coordinated transaction has rolled back its updates (YES) or not (NO)

ROLLBACKED

VARCHAR2(3)

Indicates whether the transaction has rolled back its updates (YES) or not (NO)

FBA

VARCHAR2(3)

Indicates whether the transaction is tracked by flashback archive (YES) or not (NO)

TXNEXTENT

NUMBER

Extent for the transaction

If the transaction is committed, the transaction slot of the transaction that was committed prior to this transaction. If the transaction is active, the current extent number where the undo is being written.

COLLECTING_DBARFIL

NUMBER

For distributed transactions, the collecting database relative file number

COLLECTING_DBABLK

NUMBER

For distributed transactions, the collecting database block number

PTX_XIDUSN

NUMBER

Undo segment number of the parent transaction

PTX_XIDSLT

NUMBER

Slot number of the parent transaction

PTX_XIDSQN

NUMBER

Sequence number of the parent transaction

USED_UBLK

NUMBER

Number of undo blocks used

EXTENDED_FLAG

NUMBER

Reserved for internal use

GTXNPREPPOINT

VARCHAR2(3)

Indicates whether the transaction was a prepare point (YES) or not (NO)

GTXNFORGETOK

VARCHAR2(3)

Indicates whether the transaction was a commit point, but the outcome can be forgotten (YES) or not (NO)

GTXNROLLBACK_AFTER_PREPARE

VARCHAR2(3)

Indicates whether a global transaction was rolled back after prepare (YES) or not (NO)

GTXNCOLLECTING_INFO_PUSHED

VARCHAR2(3)

Indicates whether collected information was already pushed by the prepare point (YES) or not (NO)

GTXNHABORTED

VARCHAR2(3)

Indicates whether the transaction was in-doubt and heuristically terminated (YES) or not (NO)

GTXNHCOMMITED

VARCHAR2(3)

Indicates whether the transaction was in-doubt and heuristically committed (YES) or not (NO)

PTL_SLAVETXN

VARCHAR2(3)

Indicates whether the transaction is a PTL worker process transaction (YES) or not (NO)

SESSIONLESS

VARCHAR2(3)

Indicates whether the transaction is a sessionless transaction (YES) or not (NO)

RECOVERY_SUSPENDED

VARCHAR2(3)

Indicates whether recovery has been suspended for the transaction (YES) or not (NO)

CON_ID

NUMBER

The ID of the container to which the data pertains. Possible values include:

  • 0: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

  • 1: This value is used for rows containing data that pertain to only the root

  • n: Where n is the applicable container ID for the rows containing data

Note:

This view is available starting with Oracle AI Database 26ai, Release Update 23.26.3.