ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
email@example.com. Request a Workshop for
your organization today.
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