alter table - adding columns in specific order 1

[jdmartin74]

I know it doesn't affect the working of the database, but for ease of understanding the set up later, I would like to alter an existing table and add a column to it before other columns.

Does anyone know how to do it?

 

[SantaMufasa]

JD,

Oracle adds columns to the end of an existing structure to cause zero impact upon the existing rows of data.

If you wish to add a column prior to other, existing columns, then the best method is to create another, new table (we'll call it JD) that reflects the column order that you wish.

Once that table exists, then load the new table with data from the old table:

INSERT into JD
SELECT <column-list-to-match-new-table>
FROM <old-table>;

Then drop the old table and rename the new table to the old table name:

DROP TABLE <old-table-name>;
RENAME JD to <old-table-name>;

If there are PKs, FKs, or other constraints, then you will need to accommodate them in the new table, as well.

There are a couple of variations on this same theme, but this is how life is in the Oracle World.

Let us know how it goes for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[jdmartin74]

Thanks. I had thought of that but thought there must be another way! I used MySQL a lot and you can do it with that!

 

[Beilstwh]

Another easy way to look like the order changed,


Say you have a table with four columns cola,cold,colc,colb and you want to display them in order

SQL> create table a
2 (col_b number,
3 col_a number,
4 col_c number,
5 col_d varchar2(10));

Table created.

SQL> create view a_v as
2 select col_a,col_b,col_c,col_d
3 from a;

View created.

SQL> desc a_v
Name Null? Type
------------------------------- -------- ----
COL_A NUMBER
COL_B NUMBER
COL_C NUMBER
COL_D VARCHAR2(10)


Bill
Oracle DBA/Developer
New York State, USA

 

[Welshbird]

Cor Bill - that's a cunning idea!

I'm going to use that in something straight away.

HAve a star on me.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]