"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible.
I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Provides access to SQL transaction statements from stored procedures
DISCRETE_TRANSACTION_FAILED: Cannot be performed as a discrete transaction
CONSISTENT_READ_FAILURE: Data changed by an operation that does not generate rollback data
Purge in-doubt transaction if remote database is destroyed
Follow linkto the TRANSACTION page of the library for more information on use of this proc.
When a failure occurs during commit processing, automatic recovery will consistently resolve the results at all sites involved in the transaction.
However, if the remote database is destroyed or recreated before recovery completes, then the entries used to control recovery in DBA_2PC_PENDING and associated tables will never be removed, and recovery will periodically retry.
Procedure purge_lost_db_entry allows removal of such transactions from the local site.
dbms_transaction.purge_lost_db_entry(xid IN VARCHAR2); -- tx id
When in-doubt transactions are forced to commit or rollback (instead of letting automatic recovery resolve their outcomes), there is a possibility that a transaction can have a mixed outcome: some sites commit, and others rollback.
Such inconsistency cannot be resolved automatically by Oracle; however, Oracle will flag entries in DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'. Oracle will never automatically delete information about a mixed outcome transaction.
When the application or DBA is sure all inconsistencies that might have arisen as a result of the mixed transaction have been resolved, this procedure can be used to delete the information about a given mixed outcome transaction.
dbms_transaction.purge_mixed(xid IN VARCHAR2); -- transaction id