Oracle save points

Question

I understand that when you roll back an Oracle DB to a savepoint, any savepoints marked after that original savepoint are erased, but are all committed transactions since that savepoint also rolled back? Is it a complete flashback of the database? I'm assuming it is, just want to clarify. Many thanks.

Answer 1

If you commit your transaction then all savepoints prior to that are invalidated, so "all committed transactions since that savepoint also rolled back" doesn't really make sense. You can't ever roll back a commit.

From the docs:

Using ROLLBACK with the TO SAVEPOINT clause performs the following operations:

• Rolls back just the portion of the transaction after the savepoint. It does not end the transaction.
• Erases all savepoints created after that savepoint. The named savepoint is retained, so you can roll back to the same savepoint multiple times. Prior savepoints are also retained.

The use of the "all transactions" in your question is confusing. Transactions and savepoints only apply to a single session - any transactions in other sessions are unaffected by any rollbacks you do in your session.

Rolling back your session to a savepoint does erase "any savepoints marked after that original savepoint" in that same session, which are therefore within the same transaction. It does not affect any transactions or savepoints in any other sessions, and your current transaction is still active.

So no, it is not a complete flashback of the database. Tools exist to do that, but savepoints are a completely different thing.

Read more about transactions and savepoints.