How do Savepoints work in Oracle?

Question

I'm doing pretty serious operations on my DB and I would like to keep my data safe.

Unfortunately something is going wrong, look:

SAVEPOINT before_foo_update;

CREATE TABLE AUX_FOO as
   SELECT * FROM FOO;

TRUNCATE TABLE FOO;  

ALTER TABLE FOO
  MODIFY(BAR NUMBER(11) NOT NULL);

INSERT INTO FOO
  SELECT * FROM AUX_FOO;

DROP TABLE AUX_FOO;    

ROLLBACK TO SAVEPOINT before_foo_update;

Unfortunately I'm getting an error:

ROLLBACK TO SAVEPOINT before_foo_update
Error report:
SQL Error: ORA-01086: savepoint 'BEFORE_FOO_UPDATE' never established
01086. 00000 -  "savepoint '%s' never established"
*Cause:    Trying to roll back to a save point that was never established

Now FOO table is empty... And AUX_FOO wasn't dropped. Do you know what I'm doing wrong?

Answer 1

A savepoint is a point within the current transaction. A DDL statement like CREATE TABLE, TRUNCATE, ALTER TABLE or DROP TABLE will issue an implicit commit before and after the statement runs that will end the current transaction. Once the current transaction ends, you can no longer rollback to a savepoint defined within that transaction.

Potentially, you want to look at using a restore point instead. A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point. Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore-- you would lose all changes made not just by your session or your user but all sessions and users in the database.