153 DBMS_TRANSACTION

Security Model

This package runs with the privileges of calling user, rather than the package owner SYS.

ADVISE_COMMIT Procedure

This procedure is equivalent to the SQL statement:

ALTER SESSION ADVISE COMMIT

DBMS_TRANSACTION.ADVISE_COMMIT;

ADVISE_NOTHING Procedure

This procedure is equivalent to the SQL statement:

ALTER SESSION ADVISE NOTHING

DBMS_TRANSACTION.ADVISE_NOTHING;

ADVISE_ROLLBACK Procedure

This procedure is equivalent to the SQL statement:

ALTER SESSION ADVISE ROLLBACK

DBMS_TRANSACTION.ADVISE_ROLLBACK;

COMMIT Procedure

This procedure is equivalent to the SQL statement:

COMMIT 

This procedure is included for completeness, the functionality being already implemented as part of PL/SQL.

DBMS_TRANSACTION.COMMIT;

COMMIT_COMMENT Procedure

This procedure is equivalent to the SQL statement:

COMMIT COMMENT <text>

DBMS_TRANSACTION.COMMIT_COMMENT (
   cmnt VARCHAR2);

COMMIT_FORCE Procedure

This procedure is equivalent to the SQL statement:

COMMIT FORCE <text>, <number>"

DBMS_TRANSACTION.COMMIT_FORCE (
   xid VARCHAR2, 
   scn VARCHAR2 DEFAULT NULL);

LOCAL_TRANSACTION_ID Function

This function returns the local (to instance) unique identifier for the current transaction. It returns null if there is no current transaction.

DBMS_TRANSACTION.LOCAL_TRANSACTION_ID (
   create_transaction BOOLEAN := FALSE)
  RETURN VARCHAR2;

PURGE_LOST_DB_ENTRY Procedure

When a failure occurs during commit processing, automatic recovery consistently resolves the results at all sites involved in the transaction. However, if the remote database is destroyed or re-created before recovery completes, then the entries used to control recovery in DBA_2PC_PENDING and associated tables are never removed, and recovery will periodically retry. Procedure PURGE_LOST_DB_ENTRY enables removal of such transactions from the local site.

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (
   xid VARCHAR2);

Before automatic recovery runs, the transaction may show up in DBA_2PC_PENDING as state "collecting", "committed", or "prepared". If the DBA has forced an in-doubt transaction to have a particular result by using "commit force" or "rollback force", then states "forced commit" or "forced rollback" may also appear. Automatic recovery normally deletes entries in any of these states. The only exception is when recovery finds a forced transaction which is in a state inconsistent with other sites in the transaction; in this case, the entry is left in the table and the MIXED column has the value 'yes'.

However, under certain conditions, it may not be possible for automatic recovery to run. For example, a remote database may have been permanently lost. Even if it is re-created, it gets a new database ID, so that recovery cannot identify it (a possible symptom is ORA-02062). In this case, the DBA may use the procedure PURGE_LOST_DB_ENTRY to clean up the entries in any state other than "prepared". The DBA does not need to be in any particular hurry to resolve these entries, because they are not holding any database resources.

The following table indicates what the various states indicate about the transaction and what the DBA actions should be:

PURGE_MIXED Procedure

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 flags entries in DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'.

Oracle never automatically deletes information about a mixed outcome transaction. When the application or DBA is certain that 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 VARCHAR2);

READ_ONLY Procedure

This procedure is equivalent to the SQL statement:

SET TRANSACTION READ ONLY

DBMS_TRANSACTION.READ_ONLY;

READ_WRITE Procedure

This procedure is equivalent to the SQL statement:

SET TRANSACTION READ WRITE

DBMS_TRANSACTION.READ_WRITE;

ROLLBACK Procedure

This procedure is equivalent to the SQL statement:

ROLLBACK

This procedure is included for completeness, the functionality being already implemented as part of PL/SQL.

DBMS_TRANSACTION.ROLLBACK;

ROLLBACK_FORCE Procedure

This procedure is equivalent to the SQL statement:

ROLLBACK FORCE <text>

DBMS_TRANSACTION.ROLLBACK_FORCE (
   xid VARCHAR2);

ROLLBACK_SAVEPOINT Procedure

This procedure is equivalent to the SQL statement:

ROLLBACK TO SAVEPOINT <savepoint_name>

This procedure is included for completeness, the functionality being already implemented as part of PL/SQL.

DBMS_TRANSACTION.ROLLBACK_SAVEPOINT (
   savept VARCHAR2);

SAVEPOINT Procedure

This procedure is equivalent to the SQL statement:

SAVEPOINT <savepoint_name>

This procedure is included for completeness, the feature being already implemented as part of PL/SQL.

DBMS_TRANSACTION.SAVEPOINT (
   savept VARCHAR2);

STEP_ID Function

This function returns local (to local transaction) unique positive integer that orders the DML operations of a transaction.

DBMS_TRANSACTION.STEP_ID 
   RETURN NUMBER;

USE_ROLLBACK_SEGMENT Procedure

This procedure is equivalent to the SQL statement:

SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>

DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT (
   rb_name VARCHAR2);