Oracle / PLSQL: COMMIT Statement

This Oracle tutorial explains how to use the Oracle COMMIT statement with syntax and examples.

Description

In Oracle, the COMMIT statement commits all changes for the current transaction. Once a commit is issued, other users will be able to see your changes.

Syntax

The syntax for the COMMIT statement in Oracle/PLSQL is:

COMMIT [ WORK ] [ COMMENT clause ] [ WRITE clause ] [ FORCE clause ];

Parameters or Arguments

WORK

Optional. It was added by Oracle to be SQL-compliant. Issuing the COMMIT with or without the WORK parameter will result in the same outcome.

COMMENT clause

Optional. It is used to specify a comment to be associated with the current transaction. The comment that can be up to 255 bytes of text enclosed in single quotes. It is stored in the system view called DBA_2PC_PENDING along with the transaction ID if there is a problem.

WRITE clause

Optional. It is used to specify the priority that the redo information for the committed transaction is to be written to the redo log. With this clause, you have two parameters to specify:

• WAIT or NOWAIT (WAIT is the default if omitted)
  • WAIT - means that the commit returns to the client only after the redo information is persistent in the redo log.
  • NOWAIT - means that the commit returns to the client right away regardless of the status of the redo log.
• IMMEDIATE or BATCH (IMMEDIATE is the default if omitted)
  • IMMEDIATE - forces a disk I/O causing the log writer to write the redo information to the redo log.
  • BATCH - forces a "group commit" and buffers the redo log to be written with other transactions.

FORCE clause

Optional. It is used to force the commit of a transaction that may be corrupt or in doubt. With this clause, you can specify the FORCE in 3 ways:

• FORCE 'string', [integer] or FORCE CORRUPT_XID 'string' or FORCE CORRUPT_XID_ALL
  • FORCE 'string', [integer] - allows you to commit a corrupt or in doubt transaction in a distributed database system by specifying the transaction ID in single quotes as string. You can find the transaction ID in the system view called DBA_2PC_PENDING. You can specify integer to assign the transaction a system change number if you do not wish to commit the transaction using the current system change number.
  • FORCE CORRUPT_XID 'string' - allows you to commit a corrupt or in doubt transaction by specifying the transaction ID in single quotes as string. You can find the transaction ID in the system view called V$CORRUPT_XID_LIST.
  • FORCE CORRUPT_XID_ALL - allows you to commit all corrupted transactions.

Note

• You must have DBA privileges to access the system views - DBA_2PC_PENDING and V$CORRUPT_XID_LIST.
• You must have DBA privileges to specify certain features of the COMMIT statement.

Example

Let's look at an example that shows how to issue a commit in Oracle using the COMMIT statement.

For example:

COMMIT;

This COMMIT example would perform the same as the following:

COMMIT WORK WRITE WAIT IMMEDIATE;

In this example, the WORK keyword is implied and the omission of the WRITE clause would default to WRITE WAIT IMMEDIATE so the first 2 COMMIT statements are equivalent.

Comment

Let's look at an example of a COMMIT that shows how to use the COMMENT clause:

For example, you can write the COMMIT with a comment in two ways:

COMMIT COMMENT 'This is the comment for the transaction';

OR

COMMIT WORK COMMENT 'This is the comment for the transaction';

Since the WORK keyword is always implied, both of these COMMIT examples are equivalent. The COMMIT would store the comment enclosed in quotes along with the transaction ID in the DBA_2PC_PENDING system view, if the transaction was in error or in doubt.

Force

Finally, look at an example of a COMMIT that shows how to use the FORCE clause.

For example, you can write the COMMIT of an in-doubt transaction in two ways:

COMMIT FORCE '22.14.67';

OR

COMMIT WORK FORCE '22.14.67';

Since the WORK keyword is always implied, both of these COMMIT examples would force the commit of the corrupted or in doubt transaction identified by the transaction ID '22.14.67'.