Rolling Back the DDL

Assume You were asked if You can rollback changes in tables in Oracle database, You would answer that it is not possible. DDL always makes commit before and after statement. But wait a minute: there is solution for that. In 10g we have Flashback feature. This feature can be extremely useful when you are running application patch on database. You can use this scenario:

1. issue command to set restore point:

   CREATE RESTORE POINT before_patch GUARANTEE FLASHBACK DATABASE;

2. apply application patch
3. test system after patch
4. if ok, release restore point:

   DROP RESTORE POINT before_patch;

5. if something go wrong, flashback to restore point:

   SHUTDOWN DATABASE;
   STARTUP MOUNT;
   FLASHBACK DATABASE TO RESTORE POINT before_patch;

Let me explain some issues. CREATE RESTORE POINT command has two versions: with and without GUARANTEE FLASHBACK DATABASE. When you enter GUARANTEE clause, then you must be sure that Flashback area is big enough to store all changed blocks. And at the end you must drop this restore point. Otherwise you will run into out of space in flashback area. You must also be aware that FLASHBACK DATABASE has impact on all schemas in database, so be careful.
Moreover you can use Restore Point while upgrading database 10g R1 to 10g R2. It can be very time saving in case of failure during upgrade process.
Restore Points can be also usefull in application testing process. You can define restore point, run test procedures, save results, restore to to saveponit and test again ...
To check if that you have set restore point you can run query:

select * from v$restore_point;

Hope you will find it useful,
Cheers, Paweł

Related articles:

• UNDO_SQL in FLASHBACK_TRANSACTION_QUERY
• Flashback Table and Materialized View – not working together
• More on Flashback