Oracle Plsql Rollback Statement

Oracle / PLSQL: ROLLBACK Statement

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

Description

In Oracle, the ROLLBACK statement is used to undo the work performed by the current transaction or a transaction that is in doubt.

Syntax

The syntax for the ROLLBACK statement is:

ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name  | FORCE 'string' ];

Parameters or Arguments

WORK

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

TO SAVEPOINT savepoint_name

Optional. The ROLLBACK statement undoes all changes for the current session up to the savepoint specified by savepoint_name. If this clause is omitted, then all changes are undone.

FORCE 'string'

Optional. It is used to force the rollback of a transaction that may be corrupt or in doubt. With this clause, you specify the transaction ID in single quotes as string. You can find the transaction ID in the system view called DBA_2PC_PENDING.

Note

  • You must have DBA privileges to access the system views - DBA_2PC_PENDING and V$CORRUPT_XID_LIST.
  • You can not rollback a transaction that is in doubt to a savepoint.

Example

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

For example:

ROLLBACK;

This ROLLBACK example would perform the same as the following:

ROLLBACK WORK;

In this example, the WORK keyword is implied so the first 2 ROLLBACK statements are equivalent. These examples would rollback the current transaction.

Savepoint

Let's look at an example of a ROLLBACK that shows how to use the rollback to a specific savepoint.

For example, you can write the ROLLBACK to a savepoint in two ways:

ROLLBACK TO SAVEPOINT savepoint1;

OR

ROLLBACK WORK TO SAVEPOINT savepoint1;

Since the WORK keyword is always implied, both of these ROLLBACK examples would rollback the current transaction to the savepoint called savepoint1.

Force

Finally, look at an example of a ROLLBACK that shows how to force the rollback of a transaction that is in doubt.

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

ROLLBACK FORCE '22.14.67';

OR

ROLLBACK WORK FORCE '22.14.67';

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