Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

SQL Statements (continued), 4 of 6


COMMIT

Syntax


Purpose

To end your current transaction and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle treats as a single unit. This statement also erases all savepoints in the transaction and releases the transaction's locks.


Note:

Oracle issues an implicit COMMIT before and after any data definition language (DDL) statement. 


You can also use this statement to

Oracle Corporation recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle. If you do not explicitly commit the transaction and the program terminates abnormally, the last uncommitted transaction is automatically rolled back.

A normal exit from most Oracle utilities and tools causes the current transaction to be committed. A normal exit from an Oracle precompiler program does not commit the transaction and relies on Oracle to roll back the current transaction.

See Also:

 

Prerequisites

You need no privileges to commit your current transaction.

To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Keywords and Parameters

WORK 

is supported for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.  

COMMENT  

specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.  

 

See Also: "COMMENT" for more information on adding comments to SQL statements. 

FORCE  

in a distributed database system, manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can use integer to specifically assign the transaction a system change number (SCN). If you omit integer, the transaction is committed using the current SCN.  

 

Note: A COMMIT statement with a FORCE clause commits only the specified transaction. Such a statement does not affect your current transaction.  

 

Restriction: COMMIT statements using the FORCE clause are not supported in PL/SQL.

See Also: Oracle8i Distributed Database Systems for more information on these topics. 

Examples

INSERT Example

This statement inserts a row into the DEPT table and commits this change:

INSERT INTO dept VALUES (50, 'MARKETING', 'TAMPA'); 
COMMIT WORK; 
COMMENT Example

The following statement commits the current transaction and associates a comment with it:

COMMIT 
    COMMENT 'In-doubt transaction Code 36, Call (415) 555-2637'; 

If a network or machine failure prevents this distributed transaction from committing properly, Oracle stores the comment in the data dictionary along with the transaction ID. The comment indicates the part of the application in which the failure occurred and provides information for contacting the administrator of the database where the transaction was committed.

In-Doubt Transaction Example

The following statement manually commits an in-doubt distributed transaction:

COMMIT FORCE '22.57.53'; 

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index