A transaction is committed or rolled back as a whole. Sometimes you want to undo only the last part: a step of a batch that failed, a tentative change that turned out wrong, while keeping what was done before. A savepoint marks a point in the transaction, and ROLLBACK TO SAVEPOINT undoes only the changes made after it.
Code for This Guide
The main examples are in the examples/transactions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
savepoint name; rollback to [savepoint] name;
Rolling back to a savepoint keeps the transaction open, keeps the changes made before the savepoint, and releases the locks taken after it. Savepoints set after it are erased.
Undo Part of a Transaction
The transaction raises a salary, sets a savepoint, then deletes 564 crew assignments by mistake. Rolling back to the savepoint brings them back, while the raise remains.
Example:
update employees set salary = salary + 100 where employee_id = 161;
savepoint after_raise;
delete from crew_assignments where employee_id = 140;
rollback to savepoint after_raise;
select (select salary from employees where employee_id = 161) as salary_161,
(select count(*) from crew_assignments where employee_id = 140) as crew_rows;
rollback;Output:
1 row updated.
Savepoint created.
564 rows deleted.
Rollback complete.
SALARY_161 CREW_ROWS
_____________ ____________
8900 564
Rollback complete.The final ROLLBACK undoes the raise too, ending the transaction and leaving the sample data unchanged.
Only Existing Savepoints
Example:
savepoint step_one; rollback to savepoint step_two;
Output:
Savepoint created. Error starting at line : 2 In command - rollback to savepoint step_two Error report - ORA-01086: savepoint step_two never established
Rolling back to a savepoint that was never set, or was erased by an earlier rollback, raises ORA-01086.
Things to Know
- COMMIT and a full ROLLBACK erase all savepoints of the transaction.
- Reusing a savepoint name moves it to the new position.
- In PL/SQL, an exception handler can roll back to a savepoint set at the start of a step, so one failed step does not lose the whole batch.
Related Guides
Conclusion
SAVEPOINT marks a point in a transaction, and ROLLBACK TO SAVEPOINT undoes only what came after it, keeping the earlier work and the transaction open. Use savepoints to make steps of a batch individually reversible.
See my articles more often when you search Google.
