Purging Pending Rows from the Data Dictionary

Before RECO recovers an in-doubt transaction, the transaction appears in DBA_2PC_PENDING.STATE as COLLECTING, COMMITTED, or PREPARED. If you force an in-doubt transaction using COMMIT FORCE or ROLLBACK FORCE, then the states FORCED COMMIT or FORCED ROLLBACK may appear.

Automatic recovery normally deletes entries in these states. The only exception is when recovery discovers a forced transaction that is in a state inconsistent with other sites in the transaction. In this case, the entry can be left in the table and the MIXED column in DBA_2PC_PENDING has a value of YES. These entries can be cleaned up with the DBMS_TRANSACTION.PURGE_MIXED procedure.

If automatic recovery is not possible because a remote database has been permanently lost, then recovery cannot identify the re-created database because it receives a new database ID when it is re-created. In this case, you must use the PURGE_LOST_DB_ENTRY procedure in the DBMS_TRANSACTION package to clean up the entries. The entries do not hold up database resources, so there is no urgency in cleaning them up.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_TRANSACTION package

Executing the PURGE_LOST_DB_ENTRY Procedure

To manually remove an entry from the data dictionary, use the following syntax (where trans_id is the identifier for the transaction):

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('trans_id');

For example, to purge pending distributed transaction 1.44.99, enter the following statement in SQL*Plus:

EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.44.99');

Execute this procedure only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples include:

  • Total loss of the remote database

  • Reconfiguration in software resulting in loss of two-phase commit capability

  • Loss of information from an external transaction coordinator such as a TPMonitor

Determining When to Use DBMS_TRANSACTION

The following tables indicates what the various states indicate about the distributed transaction what the administrator's action should be:

STATE Column State of Global Transaction State of Local Transaction Normal Action Alternative Action
Collecting Rolled back Rolled back None PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)
Committed Committed Committed None PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)
Prepared Unknown Prepared None Force commit or rollback
Forced commit Unknown Committed None PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)
Forced rollback Unknown Rolled back None PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)
Forced commit Mixed Committed Manually remove inconsistencies then use PURGE_MIXED -
Forced rollback Mixed Rolled back Manually remove inconsistencies then use PURGE_MIXED -