Re: Advise needed on moving data with table changes

Check out  dbms_redefinition package, it does have some restrictions though.


----- Original Message ----
From: Gints Plivna <gints.plivna@gmail.com>
To: jdunn@sefas.com
Cc: oracle-l digest users <oracle-l@freelists.org>
Sent: Monday, December 4, 2006 7:35:37 AM
Subject: Re: Advise needed on moving data with table changes


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@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


 
____________________________________________________________________________________
Any questions? Get answers on any topic at www.Answers.yahoo.com.  Try it now.

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