Oracle Database - SAVEPOINT (of a transaction)

Card Puncher Data Processing

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.

Management

Creation

savepoint name;

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

Delete

All existing savepoint are deleted with a commit

Example

update employees set age = 42 where emp_name = "gerard";
savepoint sp1;
update employees set firstname = "nico" where emp_name = "gerard";
rollback work to sp1;
commit;

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - COMMIT (of a transaction)

in oracle A commit ends the current transaction and makes permanent all changes performed in the transaction. When a transaction commits, the following actions occur: A system change number...
Toad Lock
Oracle Database - Locks

Lock Mechanism in Oracle Oracle Database provides: data concurrency, consistency. The data a session is viewing or changing must not be changed by other sessions until the user is finished. and...
Card Puncher Data Processing
Oracle Database - ROLLBACK (undo of a transaction)

Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. An implicit ROLLBACK occurs when the session (or...
Card Puncher Data Processing
Oracle Database - System Change Numbers (SCNs) - Point in time

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction....
Card Puncher Data Processing
Oracle Database - Transactions

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. A transaction begins when the first executable SQL statement is encountered. An executable...



Share this page:
Follow us:
Task Runner