oracle 如何在不删除和重新创建表的情况下在oracle中的特定位置插入列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5391280/
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
How to insert a column in a specific position in oracle without dropping and recreating the table?
提问by Amit
I have a specific scenario where i have to insert two new columns in an existing table in Oracle. I can not do the dropping and recreating the table. So can it be achieved by any means??
我有一个特定的场景，我必须在 Oracle 的现有表中插入两个新列。我无法删除和重新创建表格。那么可以通过任何方式实现吗？？
采纳答案by CJ Travis
Amit-
阿米特-
I don't believe you can add a column anywhere but at the end of the table once the table is created. One solution might be to try this:
我不相信你可以在任何地方添加一列，但一旦创建了表，就可以在表的末尾添加。一种解决方案可能是试试这个：
CREATE TABLE MY_TEMP_TABLE AS
SELECT *
FROM TABLE_TO_CHANGE;
Drop the table you want to add columns to:
删除要向其添加列的表：
DROP TABLE TABLE_TO_CHANGE;
It's at the point you could rebuild the existing table from scratch adding in the columns where you wish. Let's assume for this exercise you want to add the columns named "COL2 and COL3".
正是在这一点上，您可以从头开始重建现有表，并在您希望的列中添加。让我们假设在本练习中您要添加名为“COL2 和 COL3”的列。
Now insert the data back into the new table:
现在将数据插入到新表中：
INSERT INTO TABLE_TO_CHANGE (COL1, COL2, COL3, COL4)
SELECT COL1, 'Foo', 'Bar', COL4
FROM MY_TEMP_TABLE;
When the data is inserted into your "new-old" table, you can drop the temp table.
当数据插入“新旧”表时，您可以删除临时表。
DROP TABLE MY_TEMP_TABLE;
This is often what I do when I want to add columns in a specific location. Obviously if this is a production on-line system, then it's probably not practical, but just one potential idea.
当我想在特定位置添加列时，这通常是我所做的。显然，如果这是一个生产在线系统，那么它可能不切实际，而只是一个潜在的想法。
-CJ
-CJ
回答by Gerd
Although this is somewhat old I would like to add a slightly improved version that really changes column order. Here are the steps (assuming we have a table TAB1 with columns COL1, COL2, COL3):
虽然这有点旧，但我想添加一个稍微改进的版本，它确实改变了列顺序。以下是步骤（假设我们有一个包含 COL1、COL2、COL3 列的表 TAB1）：
- Add new column to table TAB1:
- 向表 TAB1 添加新列：
alter table TAB1 add (NEW_COL number);- "Copy" table to temp name while changing the column order AND rename the new column:
- 在更改列顺序并重命名新列的同时“复制”表到临时名称：
create table tempTAB1 as select NEW_COL as COL0, COL1, COL2, COL3 from TAB1;- drop existing table:
- 删除现有表：
drop table TAB1;- rename temp tablename to just dropped tablename:
- 将临时表名重命名为刚刚删除的表名：
rename tempTAB1 to TAB1;回答by Gerd
You (still) can not choose the position of the column using ALTER TABLE: it can only be added to the end of the table. You can obviously select the columns in any order you want, so unless you are using SELECT * FROM column order shouldn't be a big deal.
您（仍然）无法使用 ALTER TABLE 选择列的位置：它只能添加到表的末尾。您显然可以按您想要的任何顺序选择列，因此除非您使用 SELECT * FROM 列顺序，否则应该没什么大不了的。
If you really must have them in a particular order and you can't drop and recreate the table, then you might be able to drop and recreate columns instead:-
如果您确实必须按特定顺序排列它们并且您无法删除和重新创建表，那么您可以删除并重新创建列：-
First copy the table
先复制表
CREATE TABLE my_tab_temp AS SELECT * FROM my_tab;
Then drop columns that you want to be after the column you will insert
然后删除要插入的列之后的列
ALTER TABLE my_tab DROP COLUMN three;
Now add the new column (two in this example) and the ones you removed.
现在添加新列（在本例中为两个）和您删除的列。
ALTER TABLE my_tab ADD (two NUMBER(2), three NUMBER(10));
Lastly add back the data for the re-created columns
最后为重新创建的列添加数据
UPDATE my_tab SET my_tab.three = (SELECT my_tab_temp.three FROM my_tab_temp WHERE my_tab.one = my_tab_temp.one);
Obviously your update will most likely be more complex and you'll have to handle indexes and constraints and won't be able to use this in some cases (LOB columns etc). Plus this is a pretty hideous way to do this - but the table will always exist and you'll end up with the columns in a order you want. But does column order really matter that much?
显然，您的更新很可能会更复杂，您必须处理索引和约束，并且在某些情况下（LOB 列等）将无法使用它。另外，这是一种非常可怕的方法 - 但表格将始终存在，并且您最终会按照您想要的顺序排列列。但是列顺序真的那么重要吗？
回答by dr fu manchu
In 12c you can make use of the fact that columns which are set from invisible to visible are displayed as the last column of the table: Tips and Tricks: Invisible Columns in Oracle Database 12c
在 12c 中，您可以利用设置为从不可见到可见的列显示为表的最后一列这一事实： 提示和技巧：Oracle 数据库 12c 中的不可见列
Maybe that is the 'trick' @jeffrey-kemp was talking about in his comment, but the link there does not work anymore.
也许这就是@jeffrey-kemp 在他的评论中所说的“技巧”，但那里的链接不再有效。
Example:
例子：
ALTER TABLE my_tab ADD (col_3 NUMBER(10));
ALTER TABLE my_tab MODIFY (
col_1 invisible,
col_2 invisible
);
ALTER TABLE my_tab MODIFY (
col_1 visible,
col_2 visible
);
Now col_3 would be displayed first in a SELECT * FROM my_tabstatement.
现在 col_3 将首先显示在SELECT * FROM my_tab语句中。
Note: This does not change the physical order of the columns on disk, but in most cases that is not what you want to do anyway. If you really want to change the physical order, you can use the DBMS_REDEFINITION package.
注意：这不会改变磁盘上列的物理顺序，但在大多数情况下，这不是您想要做的。如果你真的想改变物理顺序，你可以使用 DBMS_REDEFINITION 包。
