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