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
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.
A new savepoint with the same name will overwrite the old one.
All existing savepoint are deleted with a commit
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;