How does one add a column to the middle of a table?

Submitted by admin on Sat, 2005-12-03 00:53
Body: 

Oracle only allows columns to be added to the end of an existing table. Example:

SQL> CREATE TABLE tab1 ( col1 NUMBER );

Table created.

SQL> ALTER TABLE tab1 ADD (col2 DATE);

Table altered.

SQL> DESC tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               DATE

Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:

ALTER TABLE tablename ADD columnname AFTER columnname;

Oracle does not support this syntax. However, it doesn't mean that it cannot be done.

Workarounds:

1. Create a new table and copy the data across.

SQL> RENAME tab1 TO tab1_old;

Table renamed.

SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;

Table created.

2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining.

»

Comments

Submitted by samar bijaya panda (not verified) on Tue, 2006-02-21 06:21.

Here is another workaround:

create table emptest as select empno, 1 as id, 'x' as emp_name, ename from emp;

In the code above, replace 1 as id, 'x' as emp_name with your new columns.

»

Submitted by Kalyani P. Banerjee (not verified) on Tue, 2006-07-25 01:14.

To add a column in middle of the table of varchar data type:

SQL>create table test (ename varchar2(20),salary number);
Table created

SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER

[i]SQL>rename test to test1;
Table renamed
[ii]SQL>create table test2 (id varchar2(20));
Table created
[iii]SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
Table created
........................................................................................
SQL>desc test;
Name Null? Type
----------------------------------------- -------- --------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER

»

Submitted by SandhyaRR on Mon, 2010-09-27 04:09.

There is a table T with col1 and col2 and you want to add a new column col3 after col1.

1. Rename the column col2 to col3 as:
ALTER TABLE tablename RENAME COLUMN col2 TO col3;

2. Add a new column to the table
alter table t1 add (col2 datatype);

3.Now finally interchange the data contained in the two column:
UPDATE EMPLOYEE
SET col2 = col3
,col3 = col2

Note: Data types of the interchanged columns should match.

»

Submitted by rleishman on Tue, 2010-10-05 17:21.

That's terrible advice. What about foreign keys? What about CHECK/NOT NULL constraints? What about indexes? What about triggers? What about Materialized Views? What about replication?

There are so many holes in this approach, you couldn't really determine whether it worked or not.

»