Re: add a new column into the middle of an existing table
Date: 30 Apr 2002 22:04:37 -0700
Message-ID: <8d4033cd.0204302104.1352c795_at_posting.google.com>
There are a couple of ways to do it, but as Daniel said, why bother?
- Export the table, drop it, recreate it with the new column order, import it with ignore=y
- 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 rowsexported
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 rowsimported
Import terminated successfully without warnings. oracle_at_nadir$ exit
SQL> desc andy
Name Null? TypeReceived on Wed May 01 2002 - 07:04:37 CEST
------------------------------- -------- ----
COL1 CHAR(2) COL3 VARCHAR2(10) COL2 VARCHAR2(10)