Oracle | DBMS_TRANSACTION

Oracle DBMS_TRANSACTION Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Provides access to SQL transaction statements from stored procedures

AUTHID CURRENT_USER

Dependencies

DBMS_AQADM_SYS	DBMS_LOGMNR_INTERNAL	DBMS_SYS_SQL
DBMS_ASSERT	DBMS_PRIV_CAPTURE	PENDING_SESSIONS$
DBMS_BLOCKCHAIN_UTL	DBMS_PRVTAQIM	PENDING_SUB_SESSIONS$
DBMS_ISCHED	DBMS_PRVTAQIP	PENDING_TRANS$
DBMS_ISCHEDFW	DBMS_SNAPSHOT_KKXRCA	SDO_GEOR
DBMS_ISCHED_REMOTE_ACCESS	DBMS_SQL

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

First Available 7.3.4

Security Model Owned by SYS with EXECUTE granted to PUBLIC.

Source {ORACLE_HOME}/rdbms/admin/dbmstrns.sql

Subprograms

ADVISE_COMMIT	GET_TRANSACTION_TIMEOUT	ROLLBACK_FORCE
ADVISE_NOTHING	GET_TRANSACTION_TYPE	ROLLBACK_SAVEPOINT
ADVISE_ROLLBACK	LOCAL_TRANSACTION_ID	SAVEPOINT
BEGIN_DISCRETE_TRANSACTION	PURGE_LOST_DB_ENTRY	START_TRANSACTION
COMMIT	PURGE_MIXED	STEP_ID
COMMIT_COMMENT	READ_ONLY	SUSPEND_TRANSACTION
COMMIT_FORCE	READ_WRITE	USE_ROLLBACK_SEGMENT
GET_TRANSACTION_ID	ROLLBACK

ADVISE_COMMIT

Equivalent to SQL "ALTER SESSION ADVISE COMMIT" dbms_transaction.advise_commit;

exec dbms_transaction.advise_commit;

      

      PL/SQL procedure successfully completed.

ADVISE_NOTHING

Equivalent to SQL "ALTER SESSION ADVISE NOTHING" dbms_transaction.advise_nothing;

exec dbms_transaction.advise_nothing;

      

      PL/SQL procedure successfully completed.

ADVISE_ROLLBACK

Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK" dbms_transaction.advise_rollback;

exec dbms_transaction.advise_rollback;

      

      PL/SQL procedure successfully completed.

BEGIN_DISCRETE_TRANSACTION

Set "discrete transaction mode" for this transaction dbms_transaction.begin_discrete_transaction;

exec dbms_transaction.begin_discrete_transaction;

      

      PL/SQL procedure successfully completed.

COMMIT

Equivalent to SQL "COMMIT" dbms_transaction.commit;

exec dbms_transaction.commit;

      

      PL/SQL procedure successfully completed.

COMMIT_COMMENT

Comment a commit statement dbms_transaction.commit_comment(cmnt IN VARCHAR2);

CREATE TABLE t (

      testcol NUMBER(2));

      

      INSERT INTO t (testcol) VALUES (1);

      

      exec dbms_transaction.commit_comment('Commiting a test record');

      

      set linesize 121

      

      SELECT local_tran_id, global_tran_id, state, mixed, advice, tran_comment

      FROM dba_2pc_pending;

COMMIT_FORCE

Equivalent to SQL "COMMIT FORCE dbms_transaction.commit_force( xid IN VARCHAR2, -- local or global transaction id scn IN VARCHAR2 DEFAULT NULL); -- system change number

exec dbms_transaction.commit_force(xid, scn);

GET_TRANSACTION_ID (new 23ai)

Return the identifier for the active XA branch or Flex transaction dbms_transaction.get_transaction_id RETURN VARCHAR2;

SELECT dbms_transaction.get_transaction_id;

      

      GET_TRANSACTION_ID

      -------------------------

GET_TRANSACTION_TIMEOUT (new 23ai)

Returns the timeout of the transaction that is active in the current session, or NULL dbms_transaction.get_transaction_timeout RETURN NUMBER;

SELECT dbms_transaction.get_transaction_timeout;

      

      GET_TRANSACTION_TIMEOUT

      -------------------------

GET_TRANSACTION_TYPE (new 23ai)

Returns the type of the transaction that is active in the current session, or NULL dbms_transaction.get_transaction_type RETURN PLS_INTEGER;

SELECT dbms_transaction.get_transaction_type;

      

      GET_TRANSACTION_TYPE

      -------------------------

LOCAL_TRANSACTION_ID

Returns the local (to instance) unique identifier for current transaction, or NULL dbms_transaction.local_transaction_id(create_transaction IN BOOLEAN := FALSE) RETURN VARCHAR2;

conn uwclass/uwclass@pdbdev

      

      SELECT dbms_transaction.local_transaction_id;

      

      CREATE TABLE t (

      testcol NUMBER(3));

      

      INSERT INTO t

      VALUES (1);

      

      SELECT dbms_transaction.local_transaction_id;

      

      CREATE OR REPLACE FUNCTION atf RETURN VARCHAR2 IS

       PRAGMA AUTONOMOUS_TRANSACTION;

       x VARCHAR2(20);

      BEGIN

        INSERT INTO t

        (testcol)

        VALUES

        (2);

      

        x := dbms_transaction.local_transaction_id;

        COMMIT;

      

        RETURN x;

      END atf;

      /

      

      set serveroutput on

      

      DECLARE

       x VARCHAR2(20);

      BEGIN

        x := atf;

        dbms_output.put_line(x);

      

        INSERT INTO t VALUES (3);

      

        x := dbms_transaction.local_transaction_id;

        dbms_output.put_line(x);

        COMMIT;

      END;

      /

PURGE_LOST_DB_ENTRY

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

exec dbms_transaction.purge_lost_db_entry(xid);

PURGE_MIXED

Purge in-doubt transaction 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

exec dbms_transaction.purge_mixed(xid);

READ_ONLY

Equivalent to SQL "SET TRANSACTION READ ONLY" dbms_transaction.read_only;

exec dbms_transaction.read_only;

      

      PL/SQL procedure successfully completed.

READ_WRITE

Equivalent to SQL "SET TRANSACTION READ WRITE" dbms_transaction.read_write;

exec dbms_transaction.read_write;

      

      PL/SQL procedure successfully completed.

ROLLBACK

Equivalent to SQL "ROLLBACK". dbms_transaction.rollback;

exec dbms_transaction.rollback;

      

      PL/SQL procedure successfully completed.

ROLLBACK_FORCE

Equivalent to SQL "ROLLBACK FORCE <text>" dbms_transaction.rollback_force(xid IN VARCHAR2); -- tx id

exec dbms_transaction.rollback_force(xid);

ROLLBACK_SAVEPOINT

Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>" dbms_transaction.rollback_savepoint(savept IN VARCHAR2);

exec dbms_transaction.rollback_savepoint('SP2');

      

      PL/SQL procedure successfully completed.

SAVEPOINT

Equivalent to SQL "SAVEPOINT <savepoint_name>" dbms_transaction.savepoint(savept IN VARCHAR2);

exec dbms_transaction.savepoint('SP2');

      

      PL/SQL procedure successfully completed.

START_TRANSACTION (new 23ai)

Start a distributed transaction of the selected type in the current session dbms_transaction.start_transaction( xid IN RAW DEFAULT NULL, transaction_type IN PLS_INTEGER DEFAULT TRANSACTION_TYPE_LOCAL, timeout IN PLS_INTEGER DEFAULT 60, flag IN PLS_INTEGER DEFAULT 0) RETURN VARCHAR2;

TBD

STEP_ID

Return local (to local transaction) unique positive integer that orders the DML operations of transaction dbms_transaction.step_id RETURN NUMBER;

SELECT dbms_transaction.step_id;

SUSPEND_TRANSACTION (new 23ai)

Undocumented dbms_transaction.suspend_transaction;

exec dbms_transaction.suspend_transaction;

      

      PL/SQL procedure successfully completed.

USE_ROLLBACK_SEGMENT

Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT 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);

-- deprecated in 9i

      

      exec dbms_transaction.use_rollback_segment('RBS01');

      

      PL/SQL procedure successfully completed.