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

Answer 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_;