Move oracle Column ID

Question

Suppose I have this table:

What I want to happen is to move/change the column id of STATUS_DT to 10 and adjust the the rest downwards like this:

Column Name | ID

...

STAT_ID     | 10
STATUS_DT   | 10
CREA_BY     | 11
CREA_DT     | 12
LAST_UPD_BY | 13
LAST_UPD_DT | 14 

Is there a single query (ALTER TABLE) so that I can achieve this without re-creating the table?

Answer 1

In theory, you could

1. rename the columns to be moved
2. add new columns to the end of the column list
3. copy data from old columns to new columns
4. drop the old columns

In practice, I'd rename the old table and recreated it with the new column order. If need be, with an online reorg...

EDIT: For example:

01 INVOICE_REQUEST_ID
   ...
09 STAT_ID
10 CREA_BY
11 CREA_DT
12 LAST_UPD_BY
13 LAST_UPD_DT
14 STATUS_DT

Then step 1) rename the columns to be moved:

ALTER TABLE my_table RENAME COLUMN crea_by     TO tmp_crea_by;
ALTER TABLE my_table RENAME COLUMN crea_dt     TO tmp_crea_dt;
ALTER TABLE my_table RENAME COLUMN last_upd_by TO tmp_last_upd_by;
ALTER TABLE my_table RENAME COLUMN last_upd_dt TO tmp_last_upd_dt;

01 INVOICE_REQUEST_ID
...
09 STAT_ID
10 TMP_CREA_BY
11 TMP_CREA_DT
12 TMP_LAST_UPD_BY
13 TMP_LAST_UPD_DT
14 STATUS_DT

Step 2) Add columns to the end of the column list:

ALTER TABLE my_table RENAME COLUMN crea_by     TO tmp_crea_by;
ALTER TABLE my_table RENAME COLUMN crea_dt     TO tmp_crea_dt;
ALTER TABLE my_table RENAME COLUMN last_upd_by TO tmp_last_upd_by;
ALTER TABLE my_table RENAME COLUMN last_upd_dt TO tmp_last_upd_dt;

01 INVOICE_REQUEST_ID
   ...
09 STAT_ID
10 TMP_CREA_BY
11 TMP_CREA_DT
12 TMP_LAST_UPD_BY
13 TMP_LAST_UPD_DT
14 STATUS_DT
15 CREA_BY
16 CREA_DT
17 LAST_UPD_BY
18 LAST_UPD_DT

Step 3) copy data from old columns to new columns:

UPDATE my_table 
   SET tmp_crea_by     = crea_by,
       tmp_crea_dt     = crea_dt,
       tmp_last_upd_by = last_upd_by,
       tmp_last_upd_dt = last_upd_dt;

Step 4) drop the old columns:

ALTER TABLE my_table SET UNUSED (tmp_crea_by, tmp_crea_dt, tmp_last_upd_by, tmp_last_upd_dt);
ALTER TABLE my_table DROP UNUSED COLUMNS;

01 INVOICE_REQUEST_ID
   ...
09 STAT_ID
10 STATUS_DT
11 CREA_BY
12 CREA_DT
13 LAST_UPD_BY
14 LAST_UPD_DT

If the data is not relevant, you can skip steps 1) rename and step 3) copy. The script would look like:

ALTER TABLE my_table SET UNUSED (crea_by, crea_dt, last_upd_by, last_upd_dt);
ALTER TABLE my_table ADD (crea_by     VARCHAR2(30));
ALTER TABLE my_table ADD (crea_dt     DATE);
ALTER TABLE my_table ADD (last_upd_by VARCHAR2(30));
ALTER TABLE my_table ADD (last_upd_dt DATE);
ALTER TABLE my_table DROP UNUSED COLUMNS;