oracle Expdp带条件,带where查询条件的expdp/impdp

最新推荐文章于 2025-06-19 13:39:05 发布

傅融

最新推荐文章于 2025-06-19 13:39:05 发布

阅读量2.4k

点赞数

文章标签： oracle Expdp带条件

本文通过实例演示了如何使用Oracle的Expdp工具，带WHERE条件导出特定ID的数据，并使用Impdp导入数据。创建表test5并插入5条数据，然后创建directory，使用Expdp导出id为1的数据，接着使用Impdp导入该特定数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试

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

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

14 ******************************************************************************

16 Dump file set for ECOLOGY.SYS_EXPORT_TABLE_01 is:

18 /data1/expdp/test5.dmp

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"'

3 Import: Release 11.2.0.1.0 - Production on Mon Nov 11 02:38:29 2019

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