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
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Provides access to SQL transaction statements from stored procedures
AUTHID
CURRENT_USER
Dependencies
DBMS_AQADM_SYS
DBMS_ISCHED_REMOTE_ACCESS
PENDING_SESSIONS$
DBMS_ASSERT
DBMS_LOGMNR_INTERNAL
PENDING_SUB_SESSIONS$
DBMS_BLOCKCHAIN_UTL
DBMS_PRVTAQIM
PENDING_TRANS$
DBMS_ISCHED
DBMS_PRVTAQIP
SDO_GEOR
DBMS_ISCHEDFW
DBMS_SNAPSHOT_KKXRCA
SYSLSBY_EDS_DDL_TRIG
Documented
Yes
Exceptions
Error Code
Reason
ORA-08175
DISCRETE_TRANSACTION_FAILED: Cannot be performed as a discrete transaction
ORA-08176
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
Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT
Deprecated
Used to select a specific named rollback segment for a transaction. This object was made obsolete by the introduction of automatic undo with the UNDO tablespace in version 9i.
dbms_transaction.use_rollback_segment(rb_name IN VARCHAR2);