expdp按日期导出linux,使用query条件进行expdp导出

刚开始从命令行导出

[oracle@orac1 ~]$ expdp justin/******* directory=pump dumpfile=justin_110217.dmp tables=(justin1，justin2) query=(justin2:"where count_date > to_date('2011-2-9','yyyy-mm-dd')",justin1:"where start_date >= to_date('2011-01-03','yyyy-mm-dd')")

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 17 February, 2011 16:47:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "justin"."SYS_EXPORT_SCHEMA_01":  justin/******** directory=pump dumpfile=justin_110217.dmp

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 299.6 GB  --两个表加起来也顶多几G而已,赶紧ctrl+c停掉了

Processing object type SCHEMA_EXPORT/USER

改用参数文件

[oracle@orac1 ~]$ vi par.par

directory=pump

dumpfile=justin_110217.dmp

tables=(justin1,justin2)

query=(justin2:"where count_date > to_date('2011-2-9','yyyy-mm-dd')",justin1:"where start_date >= to_date('2011-01-03','yyyy-mm-dd')")

[oracle@orac1 ~]$ expdp justin/******* parfile=par.par

Export: Release 10.2.0.5.0 - 64bit Production on Thursday, 17 February, 2011 16:50:38

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "justin"."SYS_EXPORT_TABLE_01":  justin/******** parfile=par.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 706 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

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/COMMENT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "justin"."justin2"    13.34 MB  334652 rows

. . exported "justin"."justin1"    26.20 MB  550193 rows

文件压缩传输到目标服务器

[oracle@testdb ~]$ impdp \'/ as sysdba\' directory=exp_dir dumpfile=justin_110217.dmp remap_schema=justin:market table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Thu Feb 17 17:21:43 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=exp_dir dumpfile=justin_110217.dmp remap_schema=justin:market table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39152: Table "MARKET"."justin1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

ORA-39152: Table "MARKET"."justin2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "MARKET"."justin2"       13.34 MB  334652 rows

. . imported "MARKET"."justin1"       26.20 MB  550193 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

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/COMMENT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 17:22:04

两个表导入前数据量

SQL> select count(*) from justin1;

COUNT(*)

----------

138026

SQL> select count(*) from justin2;

COUNT(*)

----------

70446

导入后

SQL> select count(*) from justin2;

COUNT(*)

----------

405098

SQL> select count(*) from justin1;

COUNT(*)

----------

688219