database Oracle - 将数据导入具有不同名称的表中？

Oracle -- Import data into a table with a different name?

提问by Arkady

I have a large (multi-GB) data file exported from an Oracle table. I want to import this data into another Oracle instance, but I want the table name to be differentfrom the original table. Is this possible? How?

我有一个从 Oracle 表导出的大型（多 GB）数据文件。我想将此数据导入另一个 Oracle 实例，但我希望表名与原始表不同。这可能吗？如何？

Both importing and exporting systems are Oracle 11g. The table includes a BLOB column, if this makes any difference.

导入和导出系统都是 Oracle 11g。该表包含一个 BLOB 列（如果这有任何区别的话）。

Thanks!

谢谢！

UPDATES:

更新：

The idea here was to update a table while keeping the downtime on the system that's using it to a minimum. The solution (based on Vincent Malgrat's answer and APC's update) is:

这里的想法是更新一个表，同时将使用它的系统的停机时间保持在最低限度。解决方案（基于Vincent Malgrat的回答和APC的更新）是：

1. Assuming our table name is A
2. Make a temp schema TEMP_SCHEMA
3. Import our data into TEMP_SCHEMA.A
4. CREATE REAL_SCHEMA.B AS SELECT * FROM TEMP_SCHEMA.A
5. DROP TABLE REAL_SCHEMA.ARename REAL_SCHEMA.Ato REAL_SCHEMA.A_OLD
6. Rename REAL_SCHEMA.Bto REAL_SCHEMA.A
7. DROP REAL_SCHEMA.A_OLD

1. 假设我们的表名是 A
2. 制作临时模式 TEMP_SCHEMA
3. 将我们的数据导入 TEMP_SCHEMA.A
4. CREATE REAL_SCHEMA.B AS SELECT * FROM TEMP_SCHEMA.A
5. DROP TABLE REAL_SCHEMA.A重命名REAL_SCHEMA.A为REAL_SCHEMA.A_OLD
6. 重命名REAL_SCHEMA.B为REAL_SCHEMA.A
7. DROP REAL_SCHEMA.A_OLD

This way, the downtime is only during steps 4 and 5, both should be independent of data size. I'll post an update here if this does not work :-)

这样，停机时间仅在步骤 4 和 5 期间，两者都应该与数据大小无关。如果这不起作用，我会在这里发布更新:-)

采纳答案by Vincent Malgrat

I suppose you want to import the table in a schema in which the name is already being used. I don't think you can change the table name during the import. However, you can change the schema with the FROMUSERand TOUSERoption. This will let you import the table in another (temporary) schema.

我想您想在已使用名称的模式中导入表。我认为您不能在导入过程中更改表名。但是，您可以使用FROMUSER和TOUSER选项更改架构。这将允许您在另一个（临时）模式中导入表。

When it is done copy the table to the target schema with a CREATE TABLE AS SELECT. The time it will take to copy the table will be negligible compared to the import so this won't waste too much time. You will need two times the disk space though during the operation.

完成后，将表复制到目标模式CREATE TABLE AS SELECT。与导入相比，复制表所需的时间可以忽略不计，因此不会浪费太多时间。但是在操作期间您将需要两倍的磁盘空间。

Update

更新

As suggested by Garya cleverer method would be to create a view or synonym in the temporary schema that references the new table in the target schema. You won't need to copy the data after the import as it will go through directly to the target table.

正如Gary所建议的，一个更聪明的方法是在临时模式中创建一个视图或同义词，以引用目标模式中的新表。您不需要在导入后复制数据，因为它会直接传递到目标表。

回答by APC

If you are using the old EXP and IMP utilities you cannot do this. The only option is to import into a table of the same name (although you could change the schema which owns the table.

如果您使用旧的 EXP 和 IMP 实用程序，则无法执行此操作。唯一的选择是导入同名的表（尽管您可以更改拥有该表的架构。

However, you say you are on 11g. Why not use the DataPump utility introduced in 10g, which replaces Import and Export. Because in 11g that utility offers the REMAP_TABLE option which does exactly what you want.

但是，您说您使用的是 11g。为什么不使用 10g 中引入的 DataPump 实用程序，它取代了导入和导出。因为在 11g 中，该实用程序提供了 REMAP_TABLE 选项，它完全符合您的要求。

edit

编辑

Having read the comments the OP added to another response while I was writing this, I don't think the REMAP_TABLE option will work in their case. It only renames new objects. If a table with the original name exists in the target schema the import fails with ORA-39151. Sorry.

在我写这篇文章时阅读了 OP 添加到另一个响应的评论，我认为 REMAP_TABLE 选项在他们的情况下不起作用。它只重命名新对象。如果目标模式中存在具有原始名称的表，则导入失败并显示 ORA-39151。对不起。

edit bis

编辑之二

Given the solution the OP finally chose (drop existing table, replace with new table) there is a solution with Data Pump, which is to use the TABLE_EXISTS_ACTION={TRUNCATE | REPLACE}clause. Choosing REPLACEdrops the table whereas TRUNCATEmerely, er, truncates it. In either case we have to worry about referential integrity constraints, but that is also an issue with the chosen solution.

鉴于 OP 最终选择的解决方案（删除现有表，替换为新表），有一个数据泵的解决方案，即使用TABLE_EXISTS_ACTION={TRUNCATE | REPLACE}子句。选择REPLACE删除表，而TRUNCATE只是，呃，截断它。在任何一种情况下，我们都必须担心参照完整性约束，但这也是所选解决方案的问题。

I post this addendum not for the OP but for the benefit of other seekers who find this page some time in the future.

我发布此附录不是为了 OP，而是为了将来某个时间找到此页面的其他寻求者的利益。

回答by Sakthi

Use the option REMAP_TABLE=EXISITNG_TABLE_NAME:NEW_TABLE_NAME in impdp. This works in 11gR2.

在impdp 中使用选项REMAP_TABLE=EXISITNG_TABLE_NAME:NEW_TABLE_NAME。这适用于 11gR2。

回答by Gandalf

Just import it into a table with the same name, then rename the table.

只需将其导入同名表中，然后重命名该表即可。

回答by David Aldridge

Create a view as select * from ...the table you want to import into, with the view matching the name of the table in the export. Ignore errors on import.

创建一个视图作为select * from ...要导入到的表，该视图与导出中的表的名称相匹配。忽略导入错误。