4

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

6 Answers 6

6

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!

3
  • Chris - many thanks for telling me that flashback won't work anyway. That saves me a lot of thinking.
    – Martin
    Sep 29, 2011 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, 2011 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. Sep 29, 2011 at 16:19
3

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.

1
  • 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, 2011 at 11:09
2

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

1
  • "Table is an example of an noneditionable type."
    – Martin
    Oct 21, 2014 at 18:40
1

make all your changes to a temporary set of tables.

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

1

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.

0

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

2
  • @a_horse_with_no_name I think you should replace that "Unless" with "If" Sep 16, 2013 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.
    – user1822
    Sep 16, 2013 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.