`
mikixiyou
  • 浏览: 1084762 次
  • 性别: Icon_minigender_1
  • 来自: 南京
博客专栏
C3c8d188-c0ab-3396-821d-b68331e21226
Oracle管理和开发
浏览量:348970
社区版块
存档分类
最新评论

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 ,不做处理,等待也可以。



 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics