Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from May 20 '13 at 12:26

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

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

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… . 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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.