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

secooler發表於2010-04-09
原文網址 : http://blog.itpub.net/519536/viewspace-631778/
在《【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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章