SQL Oracle 数据泵导入到现有表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13292135/
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
Oracle data pump import into existing table
提问by user1328819
i need an efficient way to copy data from one database to another. Both are based on Oracle 11g.
我需要一种将数据从一个数据库复制到另一个数据库的有效方法。两者都基于 Oracle 11g。
- Step: (Re)create new tables
- Step: Install pl sql packages
- Step: export tables tab_old1 and tab_old2 from database 1 with expdp
- Step: import into tab_new1 and tab_new2 database 2 with impdp
- +x Step: Use installed pl sql procedures
- 步骤：（重新）创建新表
- 步骤：安装pl sql包
- 步骤：使用 expdp 从数据库 1 导出表 tab_old1 和 tab_old2
- 步骤：用impdp导入tab_new1和tab_new2数据库2
- +x 步骤：使用已安装的 pl sql 程序
Challenges: The pl sql procedures uses already tab_new1 and tab_new2. Thus, I create both tables in step 1 and 2. In step 3 and 4 I import and export only data. But tab_new1 and tab_new2 have additional columns -> import fails. I tried to create views without the new columns.
挑战：pl sql 过程已经使用 tab_new1 和 tab_new2。因此，我在步骤 1 和 2 中创建了两个表。在步骤 3 和 4 中，我仅导入和导出数据。但是 tab_new1 和 tab_new2 有额外的列 -> 导入失败。我试图创建没有新列的视图。
Import fails with the following error message:
导入失败并显示以下错误消息：
ORA-31693: Table data object "Schema"."tab_old1" failed to load/unload and is being skipped due to error:
ORA-31603: object “tab_old1” of type TABLE not found in schema “Schema”
ORA-31693: Table data object "Schema"."tab_old2" failed to load/unload and is being skipped due to error:
ORA-31603: object “tab_old2” of type TABLE not found in schema “Schema”
The views are called tab_old1 and tab_old2, but of course they are not of type TABLE.
这些视图称为 tab_old1 和 tab_old2，但当然它们不是 TABLE 类型。
Any ideas?
有任何想法吗？
How can I import my data from tab_old1 in an existing table with additional columns?
如何从带有附加列的现有表中的 tab_old1 导入我的数据？
I don't want to export / import the tables in the first step, rename them, and install pl sqls procedures afterwards.
我不想在第一步中导出/导入表，重命名它们，然后安装 pl sqls 程序。
Could you help me?
你可以帮帮我吗？
edit:
编辑：
Thanks for your answer. I tried your example twice, but the remap_table function does not work for me. Import: Release 11.1.0.6.0 - 64bit Production.
感谢您的回答。我试过你的例子两次，但 remap_table 函数对我不起作用。导入：发布 11.1.0.6.0 - 64 位生产。
edit 2:Yes. It seems to be a problem of my oracle version. The remap_table function is ignored. I can write bullshit like remap_table=not.existing/table and impdp doesn't give a shit about it. Well, I don't have the time to solve this problem. I must work aroud. Thanks for your help, anyways.
编辑2：是的。好像是我oracle版本的问题。remap_table 函数被忽略。我可以写像 remap_table=not.existing/table 这样的废话，而 impdp 对此毫不在意。好吧，我没有时间解决这个问题。我必须努力工作。无论如何，谢谢你的帮助。
回答by DazzaL
ok so you are importing with content=data_only and you've renamed tab_old1 to tab_new1 and added a few columns?
好的，所以您使用 content=data_only 导入并且您已将 tab_old1 重命名为 tab_new1 并添加了几列？
when specifying the import put remap_table=tab_old1:tab_new1
指定导入时 put remap_table=tab_old1:tab_new1
this will work as long as the new columns are nullable.
只要新列可以为空，这就会起作用。
eg:
例如：
SQL> create table foo(id number);
Table created.
SQL> insert into foo select rownum from dual connect by level <= 5;
5 rows created.
SQL> commit;
Commit complete.
SQL> host expdp test/test tables=foo directory=data_pump_dir
Export: Release 11.2.0.2.0 - Production on Thu Nov 8 15:40:18 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=foo directory=data_pump_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."FOO" 5.031 KB 5 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/dtd64bit1/dpdump/expdat.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:40:30
SQL> delete from foo;
5 rows deleted.
SQL> alter table foo add (a varchar2(200));
Table altered.
SQL> alter table foo rename to foo2;
Table altered.
and your original error?
和你原来的错误？
SQL> host impdp test/test tables=foo directory=data_pump_dir content=data_only
Import: Release 11.2.0.2.0 - Production on Thu Nov 8 15:42:17 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** tables=foo directory=data_pump_dir content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-39034: Table TABLE_DATA:"TEST"."FOO" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [15]
TABLE_DATA:"TEST"."FOO"
ORA-31603: object "FOO" of type TABLE not found in schema "TEST"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8641
so remap..
所以重新映射..
SQL> host impdp test/test tables=foo remap_table=foo:foo2 directory=data_pump_dir content=data_only
Import: Release 11.2.0.2.0 - Production on Thu Nov 8 15:42:33 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_02": test/******** tables=foo remap_table=foo:foo2 directory=data_pump_dir content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."FOO2" 5.031 KB 5 rows
Job "TEST"."SYS_IMPORT_TABLE_02" successfully completed at 15:42:37
SQL> select * from foo2;
ID A
---------- ----------
1
2
3
4
5
