Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
OK, I see. So is there any possibility to roll back from table creation, truncation, modifications but not affecting other users? –  mnowotka Oct 20 '12 at 10:25
    
Restore points and FLASHBACK probably are the best way to roll back to a database state before DDL statements have been executed. Please note, however, that you need the Enterprise Edition of Oracle Database to use FLASHBACK; this feature is not available in the standard edition. See edition comparison here: oracle.com/us/products/database/product-editions-066501.html . –  user15149 Nov 7 '12 at 15:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.