ALTER TABLE tablename ADD columnname AFTER columnname;
Oracle does not support this syntax. However, it doesn't mean that it cannot be done.
一. Solution1
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;
commit;
Note: Data types of the interchanged columns should match.
二.Solution2
SQL>create table test (ename varchar2(20),salary number);
Table created
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER
SQL>rename test to test1;
Table renamed
SQL>create table test2 (id varchar2(20));
Table created
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