Re: Advise needed on moving data with table changes

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Mon, 4 Dec 2006 14:35:37 +0200
Message-ID: <6e49b6d00612040435y28a582aj5ab525f8f076d3bc@mail.gmail.com>

I'd firstly think about creating my own DML statements based on requirements I need, because I as the application developer will know best how to populate new columns/tables, what to do with the dropped columns, how to move data from the column in table A to table B etc. And there may be very unpleasant task to mix your own DML statements with a tools' generated DDL statements, because for example for "columns moved to new tables" you'd need to 1) add new column
2) move data from old column to new column (possibly discarding something and deriving something)
3) drop old column
It would be rather hard for a tool to make the 2nd step because how to know how to move the data, on what conditions?

For example looking at your requirements scenarios maybe rather complicated: 1) columns added - the possible scenarios would be at least following:

- just leave the column as null
- fill it with some default value
- fill it with some derived value from the same table
- fill it with some derived value with needing very complex logic

2) columns deleted

- just drop it
- move existing data to another column before drop
- move existing data to another columns based on some complex logic

All this looks like a small data conversion project on its own :)

Gints Plivna
http://www.gplivna.eu

2006/12/4, John Dunn <jdunn_at_sefas.com>:
> We have a new version of an application which involves lots of changes to
> tables
>
> e.g. columns added, columns deleted, columns renamed, columns moved to new
> tables
>
> We needed to upgrade the live system and move the data to the new table
> formats.
>
> What is the best approach here? What oracle utilities will handle this best?
>
>
>
> John Dunn
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Dec 04 2006 - 06:35:37 CST