This Oracle tutorial explains how to use the Oracle ROLLBACK declaration with syntax and examples.
Description
In Oracle, the ROLLBACK statement is used to undo the work performed with the aid of 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 used to be added with the aid of 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 assertion undoes all modifications for the modern session up to the savepoint designated via savepoint_name. If this clause is omitted, then all modifications are undone. FORCE ‘string’ Optional. It is used to force the rollback of a transaction that may additionally be corrupt or in doubt. With this clause, you specify the transaction ID in single quotes as string. You can discover the transaction ID in the device view known as DBA_2PC_PENDING.
Note
You have to have DBA privileges to get admission to the system views – DBA_2PC_PENDING and V$CORRUPT_XID_LIST. You can now not rollback a transaction that is in doubt to a savepoint.
Example
Let’s look at an instance that suggests 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 two ROLLBACK statements are equivalent. These examples would rollback the cutting-edge transaction.
Savepoint
Let’s seem at an example of a ROLLBACK that shows how to use the rollback to a unique 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 usually implied, each of these ROLLBACK examples would rollback the present day transaction to the savepoint known as savepoint1.
Force
Finally, look at an example of a ROLLBACK that indicates how to pressure 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 continually implied, both of these ROLLBACK examples would force the rollback of the corrupted or in doubt transaction recognized by means of the transaction ID ‘22.14.67’.
Leave a Review