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

Question

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??

Answer 1

Amit-

I don't believe you can add a column anywhere but at the end of the table once the table is created. One solution might be to try this:

CREATE TABLE MY_TEMP_TABLE AS
SELECT *
FROM TABLE_TO_CHANGE;

Drop the table you want to add columns to:

DROP TABLE TABLE_TO_CHANGE;

It's at the point you could rebuild the existing table from scratch adding in the columns where you wish. Let's assume for this exercise you want to add the columns named "COL2 and COL3".

Now insert the data back into the new table:

INSERT INTO TABLE_TO_CHANGE (COL1, COL2, COL3, COL4) 
SELECT COL1, 'Foo', 'Bar', COL4
FROM MY_TEMP_TABLE;

When the data is inserted into your "new-old" table, you can drop the temp table.

DROP TABLE MY_TEMP_TABLE;

This is often what I do when I want to add columns in a specific location. Obviously if this is a production on-line system, then it's probably not practical, but just one potential idea.

-CJ

Answer 2

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.

Answer 3

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;

Answer 4

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?