带where查询条件的expdp/impdp

测试

expdp

1> 建表

1 create table test5 ecology.test5 (id int);

 

2> 插入5条数据

1 insert into ecology.test5 vaules (1);
2 insert into ecology.test5 vaules (2);
3 insert into ecology.test5 vaules (3);
4 insert into ecology.test5 vaules (4);
5 insert into ecology.test5 vaules (5);
6 commit;

 

3> 创建directory 

1 create ditectory mydata as '/data1/expdp';
2 grant read,write on directory mydata to ecology;

 

4> 导出id为1的数据

 1 [oracle@oracle expdp]$ expdp ecology/ecology directory=mydata dumpfile=test5.dmp tables=test5 query='test5:"where id =1"'
 2 Export: Release 11.2.0.1.0 - Production on Mon Nov 11 02:26:08 2019
 3 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 4 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 5 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 6 Starting "ECOLOGY"."SYS_EXPORT_TABLE_01":  ecology/******** directory=mydata dumpfile=test5.dmp tables=test5 query=test5:"where id =1" 
 7 Estimate in progress using BLOCKS method...
 8 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 9 Total estimation using BLOCKS method: 64 KB
10 Processing object type TABLE_EXPORT/TABLE/TABLE
11 . . exported "ECOLOGY"."TEST5"                           5.023 KB       1 rows
12 Master table "ECOLOGY"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
13 
14 ******************************************************************************
15 
16 Dump file set for ECOLOGY.SYS_EXPORT_TABLE_01 is:
17 
18   /data1/expdp/test5.dmp
19 
20 Job "ECOLOGY"."SYS_EXPORT_TABLE_01" successfully completed at 02:26:19

 

impdp

首先导出test5的全部数据（省略）

以下为导出命令

 1 [oracle@oracle expdp]$ impdp ecology/ecology directory=mydata dumpfile=test5-1.dmp tables=test5 content=data_only query='test5:"where id=1"'
 2 
 3 Import: Release 11.2.0.1.0 - Production on Mon Nov 11 02:38:29 2019
 4 
 5 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 6 
 7 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
 8 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 9 Master table "ECOLOGY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
10 Starting "ECOLOGY"."SYS_IMPORT_TABLE_01":  ecology/******** directory=mydata dumpfile=test5-1.dmp tables=test5 content=data_only query=test5:"where id=1" 
11 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
12 . . imported "ECOLOGY"."TEST5"                           5.054 KB       1 out of 5 rows

13 Job "ECOLOGY"."SYS_IMPORT_TABLE_01" successfully completed at 02:38:31