【EXPDP/IMPDP】使用 EXPDP/IMPDP工具“模糊”导出和导入

在《【EXP/IMP】使用EXP /IMP工具“模糊”导出和导入》（http://space.itpub.net/519536/viewspace-629537）文章中提到EXP 与IMP工具对表的模糊导出与导入功能，EXPDP和IMPDP具有同样的功能。
简单演示，供参考。
注意通配符“_”含义。

1.环境 准备
1）创建6张表，注意表名的规律。
sec@ora10g> create table TEST_SEC  as select * from all_objects;
sec@ora10g> create table TEST_SEC1 as select * from all_objects;
sec@ora10g> create table TEST_SEC2 as select * from all_objects;
sec@ora10g> create table T_SEC     as select * from all_objects;
sec@ora10g> create table T_SEC1    as select * from all_objects;
sec@ora10g> create table T_SEC2    as select * from all_objects;

2）查看sec用户下所有的表名
sec@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_SEC                       TABLE
TEST_SEC2                      TABLE
TEST_SEC1                      TABLE
T_SEC                          TABLE
T_SEC1                         TABLE
T_SEC2                         TABLE

6 rows selected.

2.思考一下使用下面的模糊导出后为什么sec用户下所有的表均被包含在内
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=T_S%

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:16

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_TABLE_01":  sec/******** directory=expdp_dir dumpfile=sec.dmp tables=T_S%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC"                            1.013 MB   11678 rows
. . exported "SEC"."TEST_SEC1"                           1.013 MB   11678 rows
. . exported "SEC"."TEST_SEC2"                           1.013 MB   11678 rows
. . exported "SEC"."T_SEC"                               1.013 MB   11678 rows
. . exported "SEC"."T_SEC1"                              1.013 MB   11678 rows
. . exported "SEC"."T_SEC2"                              1.013 MB   11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
  /expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:22

这里是一个“美丽的误会”，本意是想备份所有以“T_S”开头的表，但是我们创建的6张表均被囊括在内了。根本原因是此处的下划线“_”表示的是通配符！

3.仅备份以“TEST”开始的表
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%

Export: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 5:55:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SEC"."SYS_EXPORT_TABLE_01":  sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SEC"."TEST_SEC"                            1.013 MB   11678 rows
. . exported "SEC"."TEST_SEC1"                           1.013 MB   11678 rows
. . exported "SEC"."TEST_SEC2"                           1.013 MB   11678 rows
Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SEC.SYS_EXPORT_TABLE_01 is:
  /expdp/sec.dmp
Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 05:55:56

OK，成功。

4.IMPDP的模糊导入功能
1）生成一份包含所有表的备份文件
ora10g@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=%

2）清除sec用户下的所有表
drop table TEST_SEC  purge;
drop table TEST_SEC1 purge;
drop table TEST_SEC2 purge;
drop table T_SEC     purge;
drop table T_SEC1    purge;
drop table T_SEC2    purge;

3）模糊导入以“TEST”开头的表
ora10g@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec.dmp tables=TEST%

Import: Release 10.2.0.3.0 - 64bit Production on Saturday, 09 April, 2010 6:08:41

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SEC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SEC"."SYS_IMPORT_TABLE_01":  sec/******** directory=expdp_dir dumpfile=sec.dmp tables=TEST%
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."TEST_SEC"                            1.018 MB   11716 rows
. . imported "SEC"."TEST_SEC1"                           1.018 MB   11717 rows
. . imported "SEC"."TEST_SEC2"                           1.018 MB   11718 rows
Job "SEC"."SYS_IMPORT_TABLE_01" successfully completed at 06:08:44

4）验证导入的数据
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST_SEC                       TABLE
TEST_SEC1                      TABLE
TEST_SEC2                      TABLE

OK，已经实现按需导入。

5.小结
充分发掘既有工具的潜在功能是提高生命质量的前提保证。人类之所以伟大，就是因为我们会灵活使用各种优秀的工具：）

Good luck.

secooler
10.04.09

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-631778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-631778/