Oracle数据泵，逻辑备份和恢复工具，他直接在逻辑从面操作，不能想象成单纯的insert ， 而且只导出数据，然后恢复数据时候，可以下面看一个试验 ,验证的是只导出数据后，，可以恢复，就算是表结构已经变化了，他也能把相应的列恢复，1)SQL> desc liuwenhe.liuwenhe;Name Null? Type----------------------------------------- -------- ----------------------------X NOT NULL NUMBER(38) Y NUMBER(38) 2)SQL> select * from liuwenhe.liuwenhe;X Y---------- ----------1 3 3 3 2 4 4 5 5 8 6 9 6 rows selected.
3)[Oracle@rac1 expdp]$ expdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe;Export: Release 11.2.0.3.0 - Production on Mon Jul 6 11:52:56 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TABLE_03": system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe 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 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "LIUWENHE"."LIUWENHE" 5.492 KB 6 rows Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:/backup/expdp/hhhf.dmp Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 11:53:10 4)SQL> alter table liuwenhe.liuwenhe drop column y;Table altered.
5)SQL> truncate table liuwenhe.liuwenhe;Table truncated.
6)[oracle@rac1 expdp]$ impdp system/manager123 directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only;Import: Release 11.2.0.3.0 - Production on Mon Jul 6 11:55:07 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TABLE_04" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TABLE_04": system/******** directory=bak dumpfile=hhhf.dmp tables=liuwenhe.liuwenhe content=data_only Processing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "LIUWENHE"."LIUWENHE" 5.492 KB 6 rows Job "SYSTEM"."SYS_IMPORT_TABLE_04" successfully completed at 11:55:13 7)SQL> select * from liuwenhe.liuwenhe;X----------1 2 3 4 5 6 6 rows selected.
总结：Oracle数据泵，逻辑备份和恢复工具，他直接在逻辑从面操作，不能想象成单纯的insert ， 而且只导出数据，然后恢复数据时候，可以不用写remap_tablespace这个参数 ，tables=liuwenhe.liuwenhe 这里要是没有写前缀liuwenhe,而写成tables=liuwenhe,系统就认为是system用户下的liuwenhe表，而出错。
本文永久更新链接地址：