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