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'.