Modifying table structure within a transaction?

Question

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

Answer 1

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!

Answer 2

The only way I can think of (short of migrating to a DBMS that does support transactional DDL) is to write your own "DDL transaction" handling where you create the corresponding statement that rolls back the actual change you did.

"Rolling back" an ADD COLUMN is quite easy as you only need to drop the column. To roll back a DROP COLUMN, the only option I see is to rename the column first and then later when everything was successful drop all renamed columns. To rollback the "virtual drop", you just need to rename the column back to it's original name.

Another alternative could be to create a copy of the tables before modifying them e.g. using CREATE TABLE backup_table AS SELECT * FROM original_table (but apparently this is not a good solution if the tables are really big)

Using Flashback is probably not very reliable as you cannot rely on the availability of the flashback data. The default value for a guaranteed flashback time is 15 minutes. But the DBA is free to choose a smaller value.

Answer 3

Oracle added a feature Edition-Based Redefinition for upgrading application schema mostly online in 11gR2. You could probably use this to accomplish your goal.

Answer 4

make all your changes to a temporary set of tables.

when everything completes successfully then appy all the changes to the real tables.

Answer 5

Oracle DDL statement implicitly commit. It's the only DBMS I work with that does.

Oracle's "FlashBack Table" can rewind a table to an earlier point in time ... but it doesn't work across a structural (i.e. DDL) change to that table.

"Flashback Database" uses a different mechanism but will take the whole database back to before the change, which you may not be allowed to do.

Another option that no-one else has mentioned is the monolith that is DBMS_REDEFINITION.
It's a huge, Oracle-supplied, package that is supposed to manage all of this for you, making your table changes transparent, seamless and avoiding [almost] any interruption to service. It's big and it's complicated but it might be worth the investment in getting to grips with it.

Answer 6

I think you should better add a structure where your clients think they are adding columns, but in fact they are only adding records in a table eg:"Table_Column_DEF". Then you only need a link table between your default Table and "Table_Column_DEF", where you also store the values.

With this method, your clients could add any column they want, and could do this in 1 transaction. You have benefits from rollback, flashback etc... You have of course some tradeoff's to consider:

1) Value Column:

• 1 value column for every possible datatype and storing everything in varchar2, clob or blob.
• Value columns for every possible datatype...

2) Performance

3) Querying: You'll have to build this dynamically. Looping through all the "Table_Column_DEF" records and adding them with a "select (select Value_Int from LinkTable where ...) Value_Int, ... from ..."