9

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, which is a problem because some of our PHP Scripts are using the first column as an identifier (one Abstract Model with more than 100 other Models using this base object...)

See also:

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
opHASnoNAME
  • 20,224
  • 26
  • 98
  • 143
  • Duplicate: http://stackoverflow.com/questions/578957/in-oracle-is-it-possible-to-insert-a-column-into-a-table – Thomas Jung Dec 01 '09 at 08:04
  • You could select the columns you need instead of *. – NitroxDM Dec 01 '09 at 08:05
  • @Nitrox: He explained the need for this workaround... – nickf Dec 01 '09 at 08:06
  • 1
    @nickf: Yeah changing the Models would be a lot of work. But so could removing and recreating the table. Then again if someone could drop the ID column the database probably doesn't rely on foreign key relationships. – NitroxDM Dec 01 '09 at 08:17
  • 3
    This just highlights one reason (of several) why you never use SELECT * in production code. The PHP scripts should specify which columns they're interested in. – Jeffrey Kemp Dec 01 '09 at 12:30
  • In 12c there is a trick that can allow changing the order of columns: http://tkyte.blogspot.com.au/2013/07/12c-silly-little-trick-with-invisibility.html – Jeffrey Kemp Jul 03 '13 at 00:55

8 Answers8

19

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;
nickf
  • 537,072
  • 198
  • 649
  • 721
  • But one have to update constraints, indexes and so on... I tried that in Oracle 11g and it works fine. In older versions I had problem that created table had columns with length of selected values - for example when there was field with type `varchar2(20)` and max length in column was 5, new table column had `varchar2(5)`. – Betlista Jun 25 '13 at 10:41
8

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.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
4

In Oracle 12c it is now easier to rearrange columns logically. It can be achived by making column invisible/visible.If you change a invisible column to visible , the column will appear last in the odering.

Consider Using Invisible Columns

Create wxyz table:

CREATE TABLE t (
    w INT,   
    y VARCHAR2,
    z VARCHAR2,
    x VARCHAR2
    
);

rearrange the x column to the middle:

    ALTER TABLE wxyz MODIFY (y INVISIBLE, z INVISIBLE);
    ALTER TABLE wxyz MODIFY (y VISIBLE, z VISIBLE);
    
    DESCRIBE wxyz;

Name
----
w 
x  
y 
z
Gnqz
  • 3,292
  • 3
  • 25
  • 35
Sulaha
  • 69
  • 3
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.

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
1

If there isn't a ton of data/columns, you could simply rename the columns in the order you want. Then just delete * from 'your table name here'. This was a good solution for me since I hadn't inserted many records yet.

0

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.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
0

Not possible to move column in oracle. It will be created in the last position. If anyone wanted so either view needs to be created or new table need to be created

Samrat Mondal
  • 1
  • This doesn't add much to the post... It should be a comment. once you [earn sufficient reputation](http://meta.stackexchange.com/questions/146472/what-is-the-best-way-to-increase-my-reputation-and-privileges) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – J. Chomel Mar 21 '17 at 11:50
-2

I use this all the time:

ALTER TABLE MOVE COLUMN column_name TO ordinal_position;
thegrinner
  • 11,546
  • 5
  • 41
  • 64
user2161700
  • 7
  • -1: this is not valid Oracle syntax as far as I can see, at least I would expect something like `ALTER TABLE table_name MOVE COLUMN column_name TO ordinal_position;`. I'm using `Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production` – Betlista Jun 25 '13 at 10:25