expdp和impdp在有条件时导出入数据

    在很多涉及数据处理的案例中，我们选择 expdp 和 impdp 的方式实现数据迁移和数据清理工作。它与 ctas 方式相比，减少日志写，也更安全。

测试案例以时间条件为例。

注意，在不同的操作系统中， query 参数值的写法不同，请注意单引号和双引号。

我这里的测试环境为 redhat  as 5.4 ，数据库版本为 10.2.0.4 。

一、条件导出

方法：

1 、     根据条件导出表的记录

expdp scott/scott directory=scott dumpfile=emp.dmplogfile=emp.log tables=emp query="' where hiredate>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/scott directory=scott dumpfile=emp.dmp logfile=emp.log tables=empquery="' where hiredate>to_date(''1981-01-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss'')'"

 

Export: Release 10.2.0.4.0 - 64bitProduction on Friday, 23 December, 2011 10:26:34

 

Copyrigxx (c) 2003, 2007, Oracle.  All rigxxs reserved.

 

Connected to: Oracle Database 10gEnterprise Edition 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=emp query=' wherehiredate>to_date(''1981-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')'

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object typeTABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object typeTABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object typeTABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported"SCOTT"."EMP"                               7.781 KB      13 rows

Master table"SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/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/scott directory=scottdumpfile=emp.dmp logfile=emp.log tables=emp content=data_only query="'where hiredate>to_date(''1982-01-01 00: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 Fri Dec 23 10:10:44 2011

 

Copyrigxx (c) 1982, 2007, Oracle.  All Rigxxs Reserved.

 

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.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/scott directory=scott dumpfile=emp.dmp logfile=emp.log tables=emp content=data_onlyquery="' where hiredate>to_date(''1982-01-01 00:00:00'',''yyyy-mm-ddhh24:mi:ss'')'"

 

Import: Release 10.2.0.4.0 - 64bitProduction on Friday, 23 December, 2011 10:20:50

 

Copyrigxx (c) 2003, 2007, Oracle.  All rigxxs reserved.

 

Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real ApplicationClusters, OLAP, Data Mining

and Real Application Testing options

Master table"SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting"SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=scottdumpfile=emp.dmp logfile=emp.log tables=emp content=data_only query=' wherehiredate>to_date(''1982-01-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')' 

Processing object type TABLE_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 Fri Dec 23 10:21:08 2011

 

Copyrigxx (c) 1982, 2007, Oracle.  All Rigxxs Reserved.

 

 

Connected to:

Oracle Database 10g Enterprise EditionRelease 10.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 ，不做处理，等待也可以。