Re: add a new column into the middle of an existing table

From: Andy <enzoweb_at_hotmail.com>
Date: 30 Apr 2002 22:04:37 -0700
Message-ID: <8d4033cd.0204302104.1352c795@posting.google.com>

There are a couple of ways to do it, but as Daniel said, why bother?

1. Export the table, drop it, recreate it with the new column order, import it with ignore=y
2. Use TOAD (www.quest.com) - go to schema browser, select the table, click on rebuild table, double-click on the columns in the top pane to move them into the bottom one, then add them again from the bottom pane to get them in the desired order. All this does is provide a GUI interface for (1) above.

1:
SQL> desc andy

 Name                            Null?    Type


 ------------------------------- -------- ----

 COL1                                     CHAR(2)
 COL2                                     VARCHAR2(10)
 COL3                                     VARCHAR2(10)

SQL> !
oracle_at_nadir$ exp oradba/PASSWORD file=a.dmp log=a.log tables=andy

Export: Release 8.0.6.0.0 - Production on Wed May 1 15:00:32 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                           ANDY          3 rows

exported
Export terminated successfully without warnings. oracle_at_nadir$ exit
SQL> drop table andy;

Table dropped.

SQL> create table andy(col1 char(2),col3 varchar2(10),col2 varchar2(10));

Table created.

SQL> desc andy

 Name                            Null?    Type


 ------------------------------- -------- ----

 COL1                                     CHAR(2)
 COL3                                     VARCHAR2(10)
 COL2                                     VARCHAR2(10)

SQL> exit
Disconnected from Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
oracle_at_nadir$ imp oradba/PASSWORD file=a.dmp full=y ignore=y

Import: Release 8.0.6.0.0 - Production on Wed May 1 15:01:31 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to: Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production

Export file created by EXPORT:V08.00.06 via conventional path . importing ORADBA's objects into ORADBA

. . importing table                         "ANDY"          3 rows

imported
Import terminated successfully without warnings. oracle_at_nadir$ exit

SQL> desc andy

 Name                            Null?    Type


 ------------------------------- -------- ----

 COL1                                     CHAR(2)
 COL3                                     VARCHAR2(10)
 COL2                                     VARCHAR2(10)

Received on Wed May 01 2002 - 00:04:37 CDT