【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 --
簡單演示,供參考。
注意萬用字元“_”含義。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle 12c expdp和impdp匯出匯入表Oracle
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- oracle10g expdp資料泵的bug,按schema匯出,匯入impdp時無jobOracle
- oracle dblink用法總結和expdp和impdp利用dblink倒入匯出到本地Oracle
- impdp和expdp用法及引數介紹
- oracle資料庫的impdp,expdpOracle資料庫
- [20200620]expdp impdp exclude引數.txt
- expdp/impdp變慢 (Doc ID 2469587.1)
- 【Data Pump】expdp/impdp Job基本管理
- expdp/impdp 詳細引數解釋
- ORACLE EXPDP IMPDP 的停止和啟動及監控Oracle
- 使用資料泵(expdp、impdp)遷移資料庫流程資料庫
- Oracle 12.1.0.2 impdp匯入慢診斷Oracle
- expdp 匯出時指定節點
- 使用impdp,expdp資料泵進入海量資料遷移
- EXP、IMP、SQLLOADER、EXPDP、IMPDP、DBMS_METADATA、SQLPLUS等方面SQL
- expdp匯出報錯ORA-39127
- Oracle expdp資料泵遠端匯出Oracle
- 基於flashback_scn的expdp匯出
- 資料泵datapump(expdp/impdp)的parfile用法,避免跳脫字元字元
- 然後再全庫匯入排除view資料庫在impdp匯入View資料庫
- exp和expdp的filesize引數的使用--匯出多個檔案
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- oracle按照表條件expdp匯出資料Oracle
- Linux下執行資料泵expdp和impdp命令,字元轉義案例兩則Linux字元
- 使用Exp和Expdp匯出資料的效能對比與最佳化
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- impdp匯入報ORA-00001 ORA-04088錯誤
- oracle 11g expdp匯出報ORA-24001Oracle
- Oracle 11g impdp 先資料後索引匯入方法Oracle索引
- 19c資料庫impdp匯入view時hang住資料庫View
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明)Oracle
- 【資料泵】EXPDP匯出表結構(真實案例)
- Oracle 11g 透過expdp按日期匯出表Oracle
- 如何確定一個dmp檔案是exp匯出的還是expdp匯出的?
- expdp在匯出時對資料大小進行評估
- expdp匯出報ORA-31693、ORA-02354、ORA-01466