Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
The DBMS_TRANSACTION package provides access to SQL transaction statements from stored procedures.
See Also:
Oracle Database SQL ReferenceThis chapter contains the following topics:
Security Model
This package runs with the privileges of calling user, rather than the package owner SYS
.
Table 111-1 DBMS_TRANSACTION Package Subprograms
Subprogram | Description |
---|---|
ADVISE_COMMIT Procedure |
Equivalent to the SQL statement:
|
ADVISE_NOTHING Procedure |
Equivalent to the SQL statement:
|
ADVISE_ROLLBACK Procedure |
Equivalent to the SQL statement:
|
BEGIN_DISCRETE_TRANSACTION Procedure |
Sets "discrete transaction mode" for this transaction |
COMMIT Procedure |
Equivalent to the SQL statement:
|
COMMIT_COMMENT Procedure |
Equivalent to the SQL statement:
|
COMMIT_FORCE Procedure |
Equivalent to the SQL statement:
|
LOCAL_TRANSACTION_ID Function |
Returns the local (to instance) unique identifier for the current transaction |
PURGE_LOST_DB_ENTRY Procedure |
Enables removal of incomplete transactions from the local site when the remote database is destroyed or re-created before recovery completes |
PURGE_MIXED Procedure |
Deletes information about a given mixed outcome transaction |
READ_ONLY Procedure |
Equivalent to the SQL statement:
|
READ_WRITE Procedure |
equivalent to the SQL statement:
|
ROLLBACK Procedure |
Equivalent to the SQL statement:
|
ROLLBACK_FORCE Procedure |
Equivalent to the SQL statement:
|
ROLLBACK_SAVEPOINT Procedure |
Equivalent to the SQL statement:
|
SAVEPOINT Procedure |
Equivalent to the SQL statement:
|
STEP_ID Function |
Returns local (to local transaction) unique positive integer that orders the DML operations of a transaction |
USE_ROLLBACK_SEGMENT Procedure |
Equivalent to the SQL statement:
|
This procedure is equivalent to the SQL statement:
ALTER SESSION ADVISE COMMIT
Syntax
DBMS_TRANSACTION.ADVISE_COMMIT;
This procedure is equivalent to the SQL statement:
ALTER SESSION ADVISE NOTHING
Syntax
DBMS_TRANSACTION.ADVISE_NOTHING;
This procedure is equivalent to the SQL statement:
ALTER SESSION ADVISE ROLLBACK
Syntax
DBMS_TRANSACTION.ADVISE_ROLLBACK;
This procedure sets "discrete transaction mode" for this transaction.
Syntax
DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION;
Exceptions
Table 111-2 BEGIN_DISCRETE_TRANSACTION Procedure Exceptions
Exception | Description |
---|---|
ORA-08175 |
A transaction attempted an operation which cannot be performed as a discrete transaction.
If this exception is encountered, then rollback and retry the transaction |
ORA-08176 |
A transaction encountered data changed by an operation that does not generate rollback data: create index, direct load or discrete transaction.
If this exception is encountered, then retry the operation that received the exception. |
Examples
DISCRETE_TRANSACTION_FAILED exception; pragma exception_init(DISCRETE_TRANSACTION_FAILED, -8175); CONSISTENT_READ_FAILURE exception; pragma exception_init(CONSISTENT_READ_FAILURE, -8176);
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.
Syntax
DBMS_TRANSACTION.COMMIT;
This procedure is equivalent to the SQL statement:
COMMIT COMMENT <text>
Syntax
DBMS_TRANSACTION.COMMIT_COMMENT ( cmnt VARCHAR2);
Parameters
Table 111-3 COMMIT_COMMENT Procedure Parameters
Parameter | Description |
---|---|
cmnt |
Comment to associate with this commit. |
This procedure is equivalent to the SQL statement:
COMMIT FORCE <text>, <number>"
Syntax
DBMS_TRANSACTION.COMMIT_FORCE ( xid VARCHAR2, scn VARCHAR2 DEFAULT NULL);
Parameters
Table 111-4 COMMIT_FORCE Procedure Parameters
Parameter | Description |
---|---|
xid |
Local or global transaction ID. |
scn |
System change number. |
This function returns the local (to instance) unique identifier for the current transaction. It returns null if there is no current transaction.
Syntax
DBMS_TRANSACTION.LOCAL_TRANSACTION_ID ( create_transaction BOOLEAN := FALSE) RETURN VARCHAR2;
Parameters
Table 111-5 LOCAL_TRANSACTION_ID Function Parameters
Parameter | Description |
---|---|
create_transaction |
If true, then start a transaction if one is not currently active. |
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.
Syntax
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ( xid VARCHAR2);
Parameters
Table 111-6 PURGE_LOST_DB_ENTRY Procedure Parameters
Parameter | Description |
---|---|
xid |
Must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table. |
Usage Notes
WARNING:
PURGE_LOST_DB_ENTRY should only be used when the other database is lost or has been re-created. Any other use may leave the other database in an unrecoverable or inconsistent state.
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:
Table 111-7 PURGE_LOST_DB_ENTRY Procedure States
State of Column | State of Global Transaction | State of Local Transaction | Normal DBA Action | Alternative DBA Action |
---|---|---|---|---|
Collecting |
Rolled back |
Rolled back |
None |
PURGE_LOST_DB_ENTRY (See Note 1) |
Committed |
Committed |
Committed |
None |
PURGE_LOST_DB_ENTRY (See Note 1) |
Prepared |
Unknown |
Prepared |
None |
FORCE COMMIT or ROLLBACK |
Forced commit |
Unknown |
Committed |
None |
PURGE_LOST_DB_ENTRY (See Note 1) |
Forced rollback |
Unknown |
Rolled back |
None |
PURGE_LOST_DB_ENTRY (See Note 1) |
Forced commit (mixed) |
Mixed |
Committed |
(See Note 2) |
|
Forced rollback (mixed) |
Mixed |
Rolled back |
(See Note 2) |
NOTE 1:
Use only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples are total loss of the remote database, reconfiguration in software resulting in loss of two-phase commit capability, or loss of information from an external transaction coordinator such as a TP monitor.NOTE 2:
Examine and take any manual action to remove inconsistencies; then use the procedurePURGE_MIXED
.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.
Syntax
DBMS_TRANSACTION.PURGE_MIXED ( xid VARCHAR2);
Parameters
Table 111-8 PURGE_MIXED Procedure Parameters
Parameter | Description |
---|---|
xid |
Must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table. |
This procedure is equivalent to the SQL statement:
SET TRANSACTION READ ONLY
Syntax
DBMS_TRANSACTION.READ_ONLY;
This procedure is equivalent to the SQL statement:
SET TRANSACTION READ WRITE
Syntax
DBMS_TRANSACTION.READ_WRITE;
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.
Syntax
DBMS_TRANSACTION.ROLLBACK;
This procedure is equivalent to the SQL statement:
ROLLBACK FORCE <text>
Syntax
DBMS_TRANSACTION.ROLLBACK_FORCE ( xid VARCHAR2);
Parameters
Table 111-9 ROLLBACK_FORCE Procedure Parameters
Parameter | Description |
---|---|
xid |
Local or global transaction ID. |
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.
Syntax
DBMS_TRANSACTION.ROLLBACK_SAVEPOINT ( savept VARCHAR2);
Parameters
Table 111-10 ROLLBACK_SAVEPOINT Procedure Parameters
Parameter | Description |
---|---|
savept |
Savepoint identifier. |
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.
Syntax
DBMS_TRANSACTION.SAVEPOINT ( savept VARCHAR2);
Parameters
This function returns local (to local transaction) unique positive integer that orders the DML operations of a transaction.
Syntax
DBMS_TRANSACTION.STEP_ID RETURN NUMBER;
This procedure is equivalent to the SQL statement:
SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>
Syntax
DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT ( rb_name VARCHAR2);
Parameters