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 working.
