[oracle] How to insert a column in a specific position in oracle without dropping and recreating the table?

I have a specific scenario where i have to insert two new columns in an existing table in Oracle. I can not do the dropping and recreating the table. So can it be achieved by any means??

You (still) can not choose the position of the column using ALTER TABLE: it can only be added to the end of the table. You can obviously select the columns in any order you want, so unless you are using SELECT * FROM column order shouldn't be a big deal.

If you really must have them in a particular order and you can't drop and recreate the table, then you might be able to drop and recreate columns instead:-

First copy the table

CREATE TABLE my_tab_temp AS SELECT * FROM my_tab;

Then drop columns that you want to be after the column you will insert

ALTER TABLE my_tab DROP COLUMN three;

Now add the new column (two in this example) and the ones you removed.

ALTER TABLE my_tab ADD (two NUMBER(2), three NUMBER(10));

Lastly add back the data for the re-created columns

UPDATE my_tab SET my_tab.three = (SELECT my_tab_temp.three FROM my_tab_temp WHERE my_tab.one = my_tab_temp.one);

Obviously your update will most likely be more complex and you'll have to handle indexes and constraints and won't be able to use this in some cases (LOB columns etc). Plus this is a pretty hideous way to do this - but the table will always exist and you'll end up with the columns in a order you want. But does column order really matter that much?

Although this is somewhat old I would like to add a slightly improved version that really changes column order. Here are the steps (assuming we have a table TAB1 with columns COL1, COL2, COL3):

1. Add new column to table TAB1:

alter table TAB1 add (NEW_COL number);

1. "Copy" table to temp name while changing the column order AND rename the new column:

create table tempTAB1 as select NEW_COL as COL0, COL1, COL2, COL3 from TAB1;

1. drop existing table:

drop table TAB1;

1. rename temp tablename to just dropped tablename:

rename tempTAB1 to TAB1;

In 12c you can make use of the fact that columns which are set from invisible to visible are displayed as the last column of the table: Tips and Tricks: Invisible Columns in Oracle Database 12c

Maybe that is the 'trick' @jeffrey-kemp was talking about in his comment, but the link there does not work anymore.

Example:

ALTER TABLE my_tab ADD (col_3 NUMBER(10));
ALTER TABLE my_tab MODIFY (
  col_1 invisible,
  col_2 invisible
);
ALTER TABLE my_tab MODIFY (
  col_1 visible,
  col_2 visible
);

Now col_3 would be displayed first in a SELECT * FROM my_tab statement.

Note: This does not change the physical order of the columns on disk, but in most cases that is not what you want to do anyway. If you really want to change the physical order, you can use the DBMS_REDEFINITION package.