Oracle: How do I reorder columns using the DBMS_REDEFINITION package?

Question

I need to reorder some columns in a table using the DBMS_REDEFINITION package. How do I do this for a table such as

create table a (z number, x number);

such that the reordered table has x as the first column?

(context: I'm writing some utilities that are adding some metadata columns to some existing tables, and it will be useful for all concerned if the metadata columns are at the front so they will be conveniently viewable when pulled up in sql developer or another table browsing tool. I'm aware of the argument that for logical processing column order doesn't matter, but this is for making the tables easier to comprehend using readily available tools.)

Dup: http://stackoverflow.com/questions/1824654/oracle-move-column-to-the-first-position — Vaibhav Desai, Jul 06 '13 at 02:43
@VaibhavDesai, not a dup, there's no mention of DBMS_REDEFINITION package. — Mark Harrison, Jul 06 '13 at 03:02
How about creating a view with the columns ordered as needed? — OldProgrammer, Jul 06 '13 at 03:15
@OldProgrammer, I would rather fix the tables than add the workaround of making views. — Mark Harrison, Jul 06 '13 at 04:30
Have you seen the documented examples? http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006859 — David Aldridge, Jul 06 '13 at 06:55
Also look at [Article by Tom Kyte](http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48asktom-089856.html) in Oracle Magazine. — ThinkJet, Jul 06 '13 at 08:40
@DavidAldridge, ThinkJet, thanks for the pointer to the examples. If nobody else comes up with a bite-sized example here I'll post mine based on these. — Mark Harrison, Jul 07 '13 at 02:51

score 1 · Accepted Answer · answered Jul 07 '13 at 06:37

1

Not sure what your question is about as the usage is explained in the manual.

But as your table does not seem to have a primary key (or you forgot to mention that) you need to use a special flag in order to be able to redefine it using dbms_redefinition:

create table a_ (x number, z number);

begin
  dbms_redefinition.start_redef_table(
    uname => user, 
    orig_table => 'A', 
    int_table => 'A_', 
    options_flag => dbms_redefinition.cons_use_rowid);

  dbms_redefinition.finish_redef_table(
    uname => user, 
    orig_table => 'A', 
    int_table => 'A_');
end;
/

drop table a_;

answered Jul 07 '13 at 06:37

Lol, if I were smart enough to figure out this clean, exact example from the manual I wouldn't have to ask on Stack Overflow! Is it the case that `start_redef_table` does not need the `col_mapping` parameter? – Mark Harrison Jul 07 '13 at 08:23
@MarkHarrison: the column mapping is only required if you rename columns if I'm not mistaken. – Jul 07 '13 at 08:28
Tested and works perfectly. I did this as SYS since the package requires the CREATE ANY ... grants. – Mark Harrison Jul 08 '13 at 17:12

score 0 · Answer 2 · answered Jul 06 '13 at 23:24

0

sorry I can't comment your question yet,
so what about rename + create as select?

alter table a rename to a_01;
create a as select x,z from a_01;

???

answered Jul 06 '13 at 23:24

Galbarad

461
3
16

The main attractions of DMBS_REDEFINITION are (a) online operation, (b) automatically takes care of indices, triggers, etc. – Mark Harrison Jul 07 '13 at 02:52

Oracle: How do I reorder columns using the DBMS_REDEFINITION package?

2 Answers2