Oracle move column to the first position

Question

Hi Folks,

is there any way to move an column in an oracle table from last to first position? Someone has dropped the ID column, and recreated it. So now it is at the end, wich is an problem because some of our PHP Scripts are using the first column as identifier (one Abstract Model with more than 100 other Models using this base object..)

See also

• In Oracle, is it possible to “insert” a column into a table?
• Inserting new columns in the middle of a table?

Answer 1

Recreating the table (via rename/temporary table so you don't lose your data) is the only way I know of.

I don't believe it's possible to simply change the column order.

Answer 2

The Oracle FAQ says:

Oracle only allows columns to be added to the end of an existing table.

You'd have to recreate your table.

RENAME tab1 TO tab1_old;

CREATE TABLE tab1 AS SELECT id, <the rest of your columns> FROM tab1_old;

Answer 3

Hi ArneRie,

the simplest way to modify the logical order of the columns of a table is to rename your table and create a view with the "right" column positions:

ALTER TABLE your_table RENAME TO your_table_t;

CREATE VIEW your_table AS SELECT <columns in the right order> FROM your_table_t;

-- grants on the view (the same as the table)
GRANT ** TO ** ON your_table;

Your application will behave as if the columns were in the "right" position. You don't have to touch at the physical structure.

Answer 4

You might like to access your table via a view, so you can painlessly rearrange the logical order if it's important to the application technology.