Using ROLLBACK Statement in Oracle

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