Changing column order

Somebody on OTN asked the question:

I would like to know if there is an Oracle feature to change the order of columns in an existing table. The table is quite huge (several 100 millions rows). I want to avoid to re-create the table and migrate the data.

I have seen this question a couple of time on the net over the last little while. I posted the answer below, and thought I might as well put it on the blog as well. The thread continued on after the initial question to include a bunch of more detail that made my answer not the correct answer. I guess I should have simply asked "Why" instead of trying to figure out what the person really wanted.

Unfortunately I have had to do this in the past:

Don't drop your table of course, but I wanted this to be complete:

DROP TABLE REORDER;

CREATE TABLE REORDER
(COLUMN4 NUMBER
,COLUMN3 NUMBER
,COLUMN2 NUMBER
,COLUMN5 NUMBER
,COLUMN1 NUMBER
,COLUMN6 NUMBER
);

Now if you select it the columns come up in the order it was created
SELECT * FROM REORDER;

You can simply select the columns in the order you want:

SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

You can create a view that the users can use if you wish:

CREATE OR REPLACE VIEW ORDERED_REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM REORDER;

SELECT * FROM ORDERED_REORDER;

clean up
DROP VIEW ORDERED_REORDER;

I have done this on SMALLER tables less than a few thousand with 100 million+ rows, I wouldn't do this without some thorough testing.

change the name of the table

RENAME REORDER TO ORIG_REORDER;

create a view to look like the original table

CREATE VIEW REORDER AS SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6 FROM ORIG_REORDER;

Now the view looks the way you want:

SELECT * FROM REORDER;

You can insert etc:
INSERT INTO REORDER (COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,COLUMN6)
VALUES (1,2,3,4,5,6);
COMMIT;

Some apps won't allow this, they need tables and won't allow views.
Test test test. But first, I would suggest getting a good business case for this as this is an oddball request. Those requests do exist but get it in writing and see if there isn't a better way to handle the request.