如何在不删除和重新创建表的情况下在oracle中的特定位置插入列? [英] How to insert a column in a specific position in oracle without dropping and recreating the table?

问题描述

我有一个特定的场景，我必须在Oracle的现有表中插入两个新列.我无法删除并重新创建表.那么可以通过任何方式实现吗?

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??

推荐答案

提交-

我不相信您可以在创建表后在表末尾的任何位置添加一列.一种解决方案可能是尝试这样做:

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;

此时，您可以从头开始重建现有表，并在所需的列中添加. 假设在此练习中，您想添加名为"COL2和COL3"的列.

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

现在将数据重新插入到新表中:

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

这篇关于如何在不删除和重新创建表的情况下在oracle中的特定位置插入列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！