Oracle Database - SAVEPOINT (of a transaction)

> Database > Oracle Database

1 - About

A savepoint is a user-declared intermediate marker within the context of a transaction. Internally, this marker resolves to an SCN. Savepoints divide a long transaction into smaller parts.

If you use savepoints in a long transaction, then you have the option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction. Thus, if you make an error, you do not need to resubmit every statement.

Savepoint naming convention: Oracle Database - Naming convention

A rollback to a savepoint in an uncommitted transaction means undoing any changes made after the specified savepoint, but it does not mean a rollback of the transaction itself.

When a transaction is rolled back to a savepoint when a “ROLLBACK TO SAVEPOINT” is performed, the following occurs:

  • Oracle Database rolls back only the statements run after the savepoint.
  • Oracle Database preserves the savepoint specified in the ROLLBACK TO SAVEPOINT statement, but all subsequent savepoints are lost.
  • Oracle Database releases all table and row locks acquired after the specified savepoint but retains all data locks acquired previous to the savepoint.

The transaction remains active and can be continued.

Advertising

3 - Management

3.1 - Creation

SAVEPOINT name;

A new savepoint with the same name will overwrite the old one.

3.2 - Delete

All existing savepoint are deleted with a commit

4 - Example

UPDATE employees SET age = 42 WHERE emp_name = "gerard";
SAVEPOINT sp1;
UPDATE employees SET firstname = "nickeau" WHERE emp_name = "gerard";
ROLLBACK WORK TO sp1;
commit;

5 - Documentation / Reference