Oracle - 将数据导入到具有不同名称的表中？ [英] Oracle -- Import data into a table with a different name?

问题描述

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

导入和导出系统都是Oracle 11g。

UPDATES

/ strong>：

这里的想法是更新表，同时将使用它的系统停机时间降到最低。解决方案（基于 Vincent Malgrat 的答案和 APC 的更新）是：

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

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

解决方案

我想你要导入表中的其中名称已在使用的模式。我不认为你可以在导入期间更改表名称。但是，您可以使用 FROMUSER 和 TOUSER 选项更改模式。

完成后，使用 CREATE TABLE将表复制到目标模式AS SELECT 。复制表所需的时间与导入相比可以忽略不计，因此这不会浪费太多时间。

更新

按 Gary 一个cleverer方法是在引用目标模式中的新表的临时模式中创建一个视图或同义词。您不需要在导入后复制数据，因为它将直接转到目标表。

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 different from the original table. Is this possible? How?

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

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:

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.A Rename REAL_SCHEMA.A to REAL_SCHEMA.A_OLD
6. Rename REAL_SCHEMA.B to 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 :-)

解决方案

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 FROMUSER and TOUSER option. This will let you import the table in another (temporary) schema.

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.

Update

As suggested by Gary a 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.

这篇关于Oracle - 将数据导入到具有不同名称的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！