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.

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

share|improve this question

5 Answers 5

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!

share|improve this answer
    
Chris - many thanks for telling me that flashback won't work anyway. That saves me a lot of thinking. –  Martin Sep 29 '11 at 13:57
    
Chris - "what your tool is for": Unfortunately this ASAM ODS standard isn't free to download but it's a database layout that allows for storing measurement data and associated metadata/attributes. Each customer has different attribute columns and may want to add or remove attribute columns. The tool should enable people to do this without being a programmer/dba. –  Martin Sep 29 '11 at 14:03
    
I'd be very wary of allowing end users to modify their database structure without the assistance of a programmer/dba. Tasks such as droppping columns can't easily be undone, so writing a tool to do this and cover all possibilities will be very difficult. You could have your data model include all the possible columns and restrict each customer's view of the columns using views; these are easily re-created with different numbers of columns - something that would be much easier to do in an app. If a customer comes up with a completely new column requirement, you'd have to include it as a patch. –  Chris Saxon Sep 29 '11 at 16:19

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.

share|improve this answer
    
Thanks. Yes doing it manually is what I would have tried. (only problem is the tool is already nearly done and this requires quite some changes) I would have done it like so: 1st run the add column statements. 2nd run the SQL statements (insert/update/delete) with one transaction. 3rd start to drop the (no longer used) columns. If something goes wrong here, it just means that the DB still contains a superfluous column that is not referenced by any app anymore (because the column was already removed from the description tables). ... –  Martin Sep 29 '11 at 11:09

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

share|improve this answer
    
"Table is an example of an noneditionable type." –  Martin 15 hours ago

make all your changes to a temporary set of tables.

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

share|improve this answer

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 ..."

share|improve this answer
    
@a_horse_with_no_name I think you should replace that "Unless" with "If" –  ypercube Sep 16 '13 at 11:07
    
What you are describing is known as the anti-pattern: "entity-attribute value". If those columns are never used in where conditions, they are never aggregated upon and you never want to run any reporting on them, it is actually acceptable. But for anything else that design is very complicated to query and basically impossible to apply proper constraint checking on it. –  a_horse_with_no_name Sep 16 '13 at 11:43

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.