移动 oracle 列 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17230039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Move oracle Column ID
提问by Christian Mark
Suppose I have this table:
假设我有这个表：
What I want to happen is to move/change the column id of STATUS_DTto 10 and adjust the the rest downwards like this:
我想要发生的是将列 id 移动/更改STATUS_DT为 10 并向下调整其余部分，如下所示：
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?
是否有一个查询 ( ALTER TABLE) 以便我可以在不重新创建表的情况下实现此目的？
回答by wolφi
In theory, you could
理论上，你可以
- rename the columns to be moved
- add new columns to the end of the column list
- copy data from old columns to new columns
- 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:
然后步骤 1) 重命名要移动的列：
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:
步骤 2) 将列添加到列列表的末尾：
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:
步骤 3) 将数据从旧列复制到新列：
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:
步骤 4) 删除旧列：
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:
如果数据不相关，您可以跳过步骤 1) 重命名和步骤 3) 复制。该脚本如下所示：
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;
