In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction.
We have a tool (written in Java) that should modify a schema by:
- adding some columns
- removing some columns
- updating a description table with the new schema layout
The tool as written will display the current schema layout to the user and allow him to modify it. (Basically adding or removing custom "attribute" columns to some tables) Once he's satisfied, he can apply his changes.
Please note: The basic schema layout, and the fact that you need to ALTER TABLE to change some things, is predefined by a standard and cannot be changed by us, as other tools wouldn't work anymore.
The problem now is that we cannot run these changes in a single transaction since, AFAIK, it's not possible to do multiple ALTER TABLE statements within a transaction.
What options do we have to "roll back" to the initial state if something goes wrong while applying the changes?
Note: Someone here proposed RESTORE POINT + FLASHBACK — is it a good idea to call this from this (Java) tool? (We do not fully control the database instance at some sites where the tool should be used.)
Note: Oracle 10g2 and above
Best Answer
As DDL implicitly commits, the only way to "rollback" your changes is to construct the reverse operation and apply it to revert the change, as a_horse_with_no_name states.
Constructing such a rollback won't always be straightforward however. If data could be written to the table between type modifications (varchar2(10) -> varchar2(50), number -> varchar2) and rolling this back then you'll also have to check the new data will be valid when reverting to the original type (or perform some conversion). Be aware that dropping columns on large tables could take some time and generate large amounts of redo.
You'll also have to be wary of invalidating any stored procedures on your database and other application dependencies as a result of these changes.
The flashback option won't help you in this instance. Once you've made DDL changes to a table, you can't restore it to it's previous state using flashback. Trying to do so will give you the error:
ORA-01466: unable to read data - table definition has changed
Flashing-back your full database would be overkill and also not possible from the Java app - you need to shut down and then mount the database to complete this operation.
Which all raises the question of what your tool is for. If you just need a GUI for people to edit tables, then something like Oracle SQL Data Modeler can do this and generate DDL scripts for you. These can then be validated, tested, an appropriate rollback constructed and applied to the database. Modifying the structure of a (production) database should be done with care and tested to ensure that all changes are valid!