Take the 2-minute tour ×

Can anybody tell me how I can add a column to table at a specific position using the DBMS_REDEFINITION package from Oracle?

share|improve this question
Please read this Oracle description and come back with specific questions. –  Yahia May 20 '13 at 10:20
Can you expand your question by stating why it even matters what order the columns are in? –  RoKa May 20 '13 at 12:28
add comment

migrated from stackoverflow.com May 20 '13 at 12:26

This question came from our site for professional and enthusiast programmers.

1 Answer

The short answer: you cannot add column to a table at a specific position.

When you add new columns they always go after the existing columns. You have to re-create your table with new definition to place a column to a position you need.

You can however create views with columns positioned arbitrarily based on your existing table, and then query or update the table directly or through your newly created views (see the section "About Views" in Database Administrator's Guide). Though some restrictions may apply to the updatable views, join views in particular (see the section "Updating a Join View" in Database Administrator's Guide).

share|improve this answer
That is not entirely true - "re-creating" a table is part of the Package the OP is asking about, see docs.oracle.com/cd/B19306_01/server.102/b14231/… . That Package helps with keeping the table accessible while redefining it... –  Yahia May 20 '13 at 10:21
@Yahia well it's true if you're using a simple "alter table ..." -- with DBMS_Redefinition you can do anything you want, but if the question is "how do I use DBMS_Redefinition" then it's rather too broad. I think Yasir's answer is worth upvoting because ultimately column order is irrelevant. –  David Aldridge May 20 '13 at 10:23
@DavidAldridge Agreed. No doubt there's an ORM that requires the order, or some other hideousness involved! Maybe some beautiful SELECT * FROM ... :) –  Phil May 20 '13 at 14:05
add comment

Your Answer

