expdp和impdp在有条件时导出入数据
在很多涉及数据处理的案例中，我们选择 expdp 和 impdp 的方式实现数据迁移和数据清理工作。它与 ctas方式相比，减少日志写，也更安全。测试案例以时间条件为例。
注意，在不同的操作系统中， query 参数值的写法不同，请注意单引号和双引号。
我这里的测试环境为 redhat as 5.4 ，数据库版本为 10.2.0.4 。
一、条件导出
方法：
1 、 根据条件导出表的记录
expdp scott/scott directory=scott dumpfile=emp.dmplogfile=emp.logtables=emp query="' wherehiredate>to_date(''1981-01-0100:00:00'',''yyyy-mm-dd hh24:mi:ss'')'"
实验过程：
/u01/oracle/home@webdg1=>XXTT1$sqlplusscott/scott
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL>
/u01/oracle/home@webdg1=>XXTT1$expdpscott/scottdirectory=scott dumpfile=emp.dmp logfile=emp.log tables=empquery="' wherehiredate>to_date(''1981-01-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss'')'"
Export: Release 10.2.0.4.0 - 64bitProduction onFriday, 23 December, 2011 10:26:34
Copyrigxx (c) 2003, 2007, Oracle. All rigxxsreserved.
Connected to: Oracle Database 10gEnterpriseEdition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real ApplicationClusters,OLAP, Data Mining
and Real Application Testing options
Starting"SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=scottdumpfile=emp.dmp logfile=emp.log tables=empquery=' wherehiredate>to_date(''1981-01-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss'')'
Estimate in progress using BLOCKS method...
Processing objecttypeTABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object typeTABLE_EXPORT/TABLE/TABLE
Processing object typeTABLE_EXPORT/TABLE/INDEX/INDEX
Processing object typeTABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing objecttypeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing objecttypeTABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object typeTABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported"SCOTT"."EMP" 7.781 KB 13 rows
Mastertable"SCOTT"."SYS_EXPORT_TABLE_01" successfullyloaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01is:
/u01/oracle/home/emp.dmp
Job"SCOTT"."SYS_EXPORT_TABLE_01"successfully completed at10:26:37
二、条件导入
方法：
1 、将原表truncate 所有数据，定义保留。
2 、根据条件导入数据
impdp scott/scottdirectory=scottdumpfile=emp.dmp logfile=emp.log tables=emp content=data_onlyquery="'where hiredate>to_date(''1982-01-0100:00:00'',''yyyy-mm-ddhh24:mi:ss'')'"
3 、检查记录数
4 、分析表与索引
实验过程：
/u01/oracle/home@webdg2=>miki$sqlplusscott/scott
SQL*Plus: Release 10.2.0.4.0 - Productionon FriDec 23 10:10:44 2011
Copyrigxx (c) 1982, 2007, Oracle. All RigxxsReserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease10.2.0.4.0 - 64bit Production
With the Partitioning, Real ApplicationClusters,OLAP, Data Mining
and Real Application Testing options
SQL> truncate table emp;
Table truncated.
/u01/oracle/home@webdg2=>miki$impdpscott/scottdirectory=scott dumpfile=emp.dmp logfile=emp.log tables=empcontent=data_onlyquery="' where hiredate>to_date(''1982-01-0100:00:00'',''yyyy-mm-ddhh24:mi:ss'')'"
Import: Release 10.2.0.4.0 - 64bitProduction onFriday, 23 December, 2011 10:20:50
Copyrigxx (c) 2003, 2007, Oracle. All rigxxsreserved.
Connected to: Oracle Database 10gEnterpriseEdition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real ApplicationClusters,OLAP, Data Mining
and Real Application Testing options
Mastertable"SCOTT"."SYS_IMPORT_TABLE_01" successfullyloaded/unloaded
Starting"SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=scottdumpfile=emp.dmp logfile=emp.log tables=empcontent=data_only query=' wherehiredate>to_date(''1982-01-0100:00:00'',''yyyy-mm-dd hh24:mi:ss'')'
Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA
. .imported"SCOTT"."EMP" 7.781 KB 3 out of 13 rows
Job"SCOTT"."SYS_IMPORT_TABLE_01"successfully completed at10:20:52
/u01/oracle/home@webdg2=>miki$sqlplusscott/scott
SQL*Plus: Release 10.2.0.4.0 - Productionon FriDec 23 10:21:08 2011
Copyrigxx (c) 1982, 2007, Oracle. All RigxxsReserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease10.2.0.4.0 - 64bit Production
With the Partitioning, Real ApplicationClusters,OLAP, Data Mining
and Real Application Testing options
SQL> select count(*) from emp;
COUNT(*)
----------
3
三、总结
实验证明， expdp 和 impdp 是可行的。 Expdp 处理 LOB 字段时速度会慢， alert.log 中会报错误，证实是 bug ，不做处理，等待也可以。
页:
[1]