Fwd: Advise needed on moving data with table changes

• Forwarded message ---------- From: rjsearle_at_gmail.com <rjsearle_at_gmail.com> Date: Dec 5, 2006 9:59 PM Subject: Re: Advise needed on moving data with table changes To: jdunn_at_sefas.com

A different approach that I've used as part of a table redef, data archive, space consolidation exercise is to use materialised views that build a *new* version of the existing table (tablename) called tablename_new. In the view def you add/delete the columns as required and maybe take the opportunity to only take the rows necessary and leave the rest behind in the original table. When this is done for all tables (space permitting of course) then for each table you drop the MV def and keep the new table, rename tablename to tablename_old, rename tablename_new to tablename. Space management can be tricky so you could do this in a new DB, schema (or not) Naturally, take an archive copy of all the target tables before you do this, especially if you are selective about which records you keep.

The benefit of this approach is that you can do 98% of the work without any real impact on users. the rename should probably be done with exclusive access but the process is very quick

HTH
Russell

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 05 2006 - 06:00:38 CST