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

User · Answer

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.

User · Answer

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

User · Answer

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     Add new column to table TAB1     alter table TAB1 add  NEW COL number      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    drop existing table    drop table TAB1    rename temp tablename to just dropped tablename    rename tempTAB1 to TAB1

User · Answer

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

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

Examples related to oracle

Examples related to oracle11g