The syntax for the ROLLBACK statement is:
ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name | FORCE 'string' ];
Parameters or Arguments
- 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.
Let's look at an example that shows how to issue a rollback in Oracle using the ROLLBACK statement.
This ROLLBACK example would perform the same as the following:
In this example, the WORK keyword is implied so the first 2 ROLLBACK statements are equivalent. These examples would rollback the current transaction.
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;
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.
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';
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'.