Viewing Information About Distributed Transactions

The data dictionary of each database stores information about all open distributed transactions. You can use data dictionary tables and views to gain information about the transactions. This section contains the following topics:

Determining the ID Number and Status of Prepared Transactions
Tracing the Session Tree of In-Doubt Transactions

Determining the ID Number and Status of Prepared Transactions

The following view shows the database links that have been defined at the local database and stored in the data dictionary:

View	Purpose
`DBA_2PC_PENDING`	Lists all in-doubt distributed transactions. The view is empty until populated by an in-doubt transaction. After the transaction is resolved, the view is purged.

Use this view to determine the global commit number for a particular transaction ID. You can use this global commit number when manually resolving an in-doubt transaction.

The following table shows the most relevant columns (for a description of all the columns in the view, see Oracle Database Reference):

Table 33-1 DBA_2PC_PENDING

Column	Description
`LOCAL_TRAN_ID`	Local transaction identifier in the format integer.integer.integer. Note: When the `LOCAL_TRAN_ID` and the `GLOBAL_TRAN_ID` for a connection are the same, the node is the global coordinator of the transaction.
`GLOBAL_TRAN_ID`	Global database identifier in the format global_db_name.db_hex_id.local_tran_id, where db_hex_id is an eight-character hexadecimal value used to uniquely identify the database. This common transaction ID is the same on every node for a distributed transaction. Note: When the `LOCAL_TRAN_ID` and the `GLOBAL_TRAN_ID` for a connection are the same, the node is the global coordinator of the transaction.
`STATE`	`STATE` can have the following values: Collecting This category normally applies only to the global coordinator or local coordinators. The node is currently collecting information from other database servers before it can decide whether it can prepare. Prepared The node has prepared and may or may not have acknowledged this to its local coordinator with a prepared message. However, no commit request has been received. The node remains prepared, holding any local resource locks necessary for the transaction to commit. Committed The node (any type) has committed the transaction, but other nodes involved in the transaction may not have done the same. That is, the transaction is still pending at one or more nodes. Forced Commit A pending transaction can be forced to commit at the discretion of a database administrator. This entry occurs if a transaction is manually committed at a local node. Forced termination (rollback) A pending transaction can be forced to roll back at the discretion of a database administrator. This entry occurs if this transaction is manually rolled back at a local node.
`MIXED`	`YES` means that part of the transaction was committed on one node and rolled back on another node.
`TRAN_COMMENT`	Transaction comment or, if using transaction naming, the transaction name is placed here when the transaction is committed.
`HOST`	Name of the host machine.
`COMMIT#`	Global commit number for committed transactions.

Execute the following script, named pending_txn_script, to query pertinent information in DBA_2PC_PENDING (sample output included):

COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A30
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
   FROM DBA_2PC_PENDING
/

SQL> @pending_txn_script

LOCAL_TRAN_ID GLOBAL_TRAN_ID                 STATE    MIX HOST       COMMIT#
------------- ------------------------------ -------- --- ---------- ----------
1.15.870      HQ.ACME.COM.ef192da4.1.15.870  commit   no  dlsun183   115499

This output indicates that local transaction 1.15.870 has been committed on this node, but it may be pending on one or more other nodes. Because LOCAL_TRAN_ID and the local part of GLOBAL_TRAN_ID are the same, the node is the global coordinator of the transaction.

Tracing the Session Tree of In-Doubt Transactions

The following view shows which in-doubt transactions are incoming from a remote client and which are outgoing to a remote server:

View	Purpose
`DBA_2PC_NEIGHBORS`	Lists all incoming (from remote client) and outgoing (to remote server) in-doubt distributed transactions. It also indicates whether the local node is the commit point site in the transaction. The view is empty until populated by an in-doubt transaction. After the transaction is resolved, the view is purged.

When a transaction is in-doubt, you may need to determine which nodes performed which roles in the session tree. Use to this view to determine:

All the incoming and outgoing connections for a given transaction
Whether the node is the commit point site in a given transaction
Whether the node is a global coordinator in a given transaction (because its local transaction ID and global transaction ID are the same)

The following table shows the most relevant columns (for an account of all the columns in the view, see Oracle Database Reference):

Table 33-2 DBA_2PC_NEIGHBORS

Column	Description
`LOCAL_TRAN_ID`	Local transaction identifier with the format integer.integer.integer. Note: When `LOCAL_TRAN_ID` and `GLOBAL_TRAN_ID`.`DBA_2PC_PENDING` for a connection are the same, the node is the global coordinator of the transaction.
`IN_OUT`	`IN` for incoming transactions; `OUT` for outgoing transactions.
`DATABASE`	For incoming transactions, the name of the client database that requested information from this local node; for outgoing transactions, the name of the database link used to access information on a remote server.
`DBUSER_OWNER`	For incoming transactions, the local account used to connect by the remote database link; for outgoing transactions, the owner of the database link.
`INTERFACE`	`C` is a commit message; `N` is either a message indicating a prepared state or a request for a read-only commit. When `IN_OUT` is `OUT`, `C` means that the child at the remote end of the connection is the commit point site and knows whether to commit or terminate. `N` means that the local node is informing the remote node that it is prepared. When `IN_OUT` is `IN`, `C` means that the local node or a database at the remote end of an outgoing connection is the commit point site. `N` means that the remote node is informing the local node that it is prepared.

Execute the following script, named neighbors_script, to query pertinent information in DBA_2PC_PENDING (sample output included):

COL LOCAL_TRAN_ID FORMAT A13
COL IN_OUT FORMAT A6
COL DATABASE FORMAT A25
COL DBUSER_OWNER FORMAT A15
COL INTERFACE FORMAT A3
SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, DBUSER_OWNER, INTERFACE 
   FROM DBA_2PC_NEIGHBORS
/

SQL> CONNECT SYS/password@hq.acme.com
SQL> @neighbors_script

LOCAL_TRAN_ID IN_OUT DATABASE                  DBUSER_OWNER    INT
------------- ------ ------------------------- --------------- ---
1.15.870      out    SALES.ACME.COM            SYS             C

This output indicates that the local node sent an outgoing request to remote server sales to commit transaction 1.15.870. If sales committed the transaction but no other node did, then you know that sales is the commit point site, because the commit point site always commits first.