Re: Restructuring Oracle tables

David Crossley <Rhona_Crossley_at_bc.sympatico.ca> wrote:
>I am currently running an Oracle 7.1 database on AIX and will be
>migrating to Oracle 7.2.3. Before I migrate to 7.2.3, I would like
>to perform some housekeeping on my tables. A number of the tables have
>extraneous fields as well as fields that need to be resized. What is
>the recommended method ? Should I export the tables, drop the
>orginal table and reimport the data ? Is the export/import process
>smart enough to know which fields have been deleted ? Any suggestions
>would be appreciated.
>
>
>David Crossley

Hi there,

Importing a table into a destination schema which does not have a particular column will result in 'Invalid column name' and the import will fail. There are two ways of doing this.

1. In schema1, drop all reduntant columns by create table t1_old (col1, col2, col3) as select col1, col2, col3 from t1

   create all constraints on t1_old;

   Drop table t1;

   rename t1_old to t1;

   Now export the above table and import in the destination database.

2. Second method, generate a comma delimited file of the selected columns from t1. Use SQL*LOad to read the comma delimited files and import into schema 2.

The disadvantage is that, you need to create all constraints in destination database.

Good Luck,

N.Prabhakar Received on Sun Dec 01 1996 - 00:00:00 CET