【EXP/IMP】使用EXP /IMP工具“模糊”匯出和匯入
EXP/IMP這兩個邏輯備份和恢復工具想必大家都已經達到熟練應用的境界，不過其中隱藏的“模糊”匯出和“模糊”匯入功能也許依然會使您眼前一亮。
1.在全新的使用者sec下建立四張表，同時給每張表中初始化一條資料
sec@ora10g> select * from tab;
no rows selected
sec@ora10g> create table t_1 (x int);
sec@ora10g> insert into t_1 values (1);
sec@ora10g> create table t_2 (x int);
sec@ora10g> insert into t_2 values (2);
sec@ora10g> create table sec_1secooler (x int);
sec@ora10g> insert into sec_1secooler values (3);
sec@ora10g> create table sec_2secooler (x int);
sec@ora10g> insert into sec_2secooler values (4);
sec@ora10g> commit;
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_1 TABLE
T_2 TABLE
SEC_1SECOOLER TABLE
SEC_2SECOOLER TABLE
2.Windows環境下模糊匯出示例
1）模糊匯出以“T_”開頭的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(t_%)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:51 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T_1 1 rows exported
. . exporting table T_2 1 rows exported
Export terminated successfully without warnings.
2）模糊匯出以“SECOOLER”結尾的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(%secooler)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:57 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
3.Windows環境下模糊匯入示例
1）先生成一個包含所有表的備份（同樣使用模糊匹配的方法）
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:41:49 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table T_2 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
2）將備份中的以“T_”開頭的表匯入到secooler使用者中
C:\>imp secooler/secooler@conn_to_secDB file=sec.dmp tables=t_% fromuser=sec touser=secooler
Import: Release 10.2.0.3.0 - Production on Mon Mar 15 21:43:41 2010
Copyright (c) 1982, 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
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SEC, not by you
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SEC's objects into SECOOLER
. . importing table "T_2" 1 rows imported
. . importing table "T_1" 1 rows imported
Import terminated successfully without warnings.
OK，目的達到。
4.Linux環境下模糊匯出示例
1）模糊匯出以“T_”開頭的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(t%\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:49:26 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_1 1 rows exported
. . exporting table T_2 1 rows exported
Export terminated successfully without warnings.
2）模糊匯出以“SECOOLER”結尾的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%secooler\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:21 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
在Linux環境中匯出資料如果使用的是一行命令（包含括號），需要注意括號的轉義處理。
怎麼規避這個括號的轉義呢？
第一種方法：
不使用括號，效果是一樣的
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=%secooler,%1%
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:55:51 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
第二種方法：
將帶括號的引數寫入到引數檔案，使用parfile引數呼叫。
ora10g@secDB /exp$ vi sec.par
userid=sec/sec
file=sec.dmp
log=sec.log
tables=(%SEcooler,%1%)
~
~
ora10g@secDB /exp$ exp parfile=sec.par
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:57:57 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
5.Linux環境下模糊匯入示例
將sec.dmp中的包含關鍵字“1”的表匯入到secooler使用者。
ora10g@secDB /exp$ imp secooler/secooler file=sec.dmp ignore=y tables=%1% fromuser=sec touser=secooler
Import: Release 10.2.0.3.0 - Production on Mon Mar 15 13:59:50 2010
Copyright (c) 1982, 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
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SEC, not by you
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SECOOLER
. . importing table "SEC_1SECOOLER" 1 rows imported
. . importing table "T_1" 1 rows imported
Import terminated successfully without warnings.
6.小貼士
無論在Windows環境還是Linux環境，tables引數內容都不區分大小寫
1）驗證Windows環境下執行效果
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%SECooler
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:39:58 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
2）驗證Linux環境下執行效果
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%SECooler\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:33 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
7.小結
使用EXP工具可以完成模糊匯出，使用IMP命令亦可以完成模糊匯入。這兩個小技巧給我們日常備份和恢復帶來非常大的裨益的同時，也提高了我們備份恢復的靈活性。
分享在此，供參考。
Good luck.
secooler
10.03.15
-- The End --
1.在全新的使用者sec下建立四張表，同時給每張表中初始化一條資料
sec@ora10g> select * from tab;
no rows selected
sec@ora10g> create table t_1 (x int);
sec@ora10g> insert into t_1 values (1);
sec@ora10g> create table t_2 (x int);
sec@ora10g> insert into t_2 values (2);
sec@ora10g> create table sec_1secooler (x int);
sec@ora10g> insert into sec_1secooler values (3);
sec@ora10g> create table sec_2secooler (x int);
sec@ora10g> insert into sec_2secooler values (4);
sec@ora10g> commit;
sec@ora10g> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T_1 TABLE
T_2 TABLE
SEC_1SECOOLER TABLE
SEC_2SECOOLER TABLE
2.Windows環境下模糊匯出示例
1）模糊匯出以“T_”開頭的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(t_%)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:51 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table T_1 1 rows exported
. . exporting table T_2 1 rows exported
Export terminated successfully without warnings.
2）模糊匯出以“SECOOLER”結尾的表
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=(%secooler)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:25:57 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
3.Windows環境下模糊匯入示例
1）先生成一個包含所有表的備份（同樣使用模糊匹配的方法）
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:41:49 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table T_2 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
2）將備份中的以“T_”開頭的表匯入到secooler使用者中
C:\>imp secooler/secooler@conn_to_secDB file=sec.dmp tables=t_% fromuser=sec touser=secooler
Import: Release 10.2.0.3.0 - Production on Mon Mar 15 21:43:41 2010
Copyright (c) 1982, 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
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SEC, not by you
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SEC's objects into SECOOLER
. . importing table "T_2" 1 rows imported
. . importing table "T_1" 1 rows imported
Import terminated successfully without warnings.
OK，目的達到。
4.Linux環境下模糊匯出示例
1）模糊匯出以“T_”開頭的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(t%\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:49:26 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_1 1 rows exported
. . exporting table T_2 1 rows exported
Export terminated successfully without warnings.
2）模糊匯出以“SECOOLER”結尾的表
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%secooler\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:21 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
在Linux環境中匯出資料如果使用的是一行命令（包含括號），需要注意括號的轉義處理。
怎麼規避這個括號的轉義呢？
第一種方法：
不使用括號，效果是一樣的
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=%secooler,%1%
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:55:51 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
第二種方法：
將帶括號的引數寫入到引數檔案，使用parfile引數呼叫。
ora10g@secDB /exp$ vi sec.par
userid=sec/sec
file=sec.dmp
log=sec.log
tables=(%SEcooler,%1%)
~
~
ora10g@secDB /exp$ exp parfile=sec.par
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:57:57 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
. . exporting table T_1 1 rows exported
Export terminated successfully without warnings.
5.Linux環境下模糊匯入示例
將sec.dmp中的包含關鍵字“1”的表匯入到secooler使用者。
ora10g@secDB /exp$ imp secooler/secooler file=sec.dmp ignore=y tables=%1% fromuser=sec touser=secooler
Import: Release 10.2.0.3.0 - Production on Mon Mar 15 13:59:50 2010
Copyright (c) 1982, 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
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SEC, not by you
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SECOOLER
. . importing table "SEC_1SECOOLER" 1 rows imported
. . importing table "T_1" 1 rows imported
Import terminated successfully without warnings.
6.小貼士
無論在Windows環境還是Linux環境，tables引數內容都不區分大小寫
1）驗證Windows環境下執行效果
C:\>exp sec/sec@conn_to_secDB file=sec.dmp tables=%SECooler
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 21:39:58 2010
Copyright (c) 1982, 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
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
2）驗證Linux環境下執行效果
ora10g@secDB /exp$ exp sec/sec file=sec.dmp log=sec.log tables=\(%SECooler\)
Export: Release 10.2.0.3.0 - Production on Mon Mar 15 13:50:33 2010
Copyright (c) 1982, 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
Export done in AL32UTF8 character set and UTF8 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table SEC_1SECOOLER 1 rows exported
. . exporting table SEC_2SECOOLER 1 rows exported
Export terminated successfully without warnings.
7.小結
使用EXP工具可以完成模糊匯出，使用IMP命令亦可以完成模糊匯入。這兩個小技巧給我們日常備份和恢復帶來非常大的裨益的同時，也提高了我們備份恢復的靈活性。
分享在此，供參考。
Good luck.
secooler
10.03.15
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1251220/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- exp/imp匯出匯入工具的使用
- Oracle exp/imp匯出匯入工具的使用Oracle
- 資料匯入匯出EXP/IMP
- exp/imp匯出匯入資料
- Oracle匯入(imp )與匯出(exp )Oracle
- ORACLE匯入匯出命令exp/impOracle
- oracle資料匯出匯入（exp/imp）Oracle
- Oracle 遠端匯出匯入 imp/expOracle
- Oracle資料匯入匯出imp/exp命令Oracle
- oracle資料的匯入匯出imp/expOracle
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- Oracle資料匯入匯出imp/exp命令(轉)Oracle
- imp exp 跨系統匯入案例
- 特殊符號密碼處理 - 匯入匯出exp/imp符號密碼
- 使用exp/imp匯出匯入資料（邏輯備份恢復）
- 使用Oracle 的 imp ,exp 命令實現資料的匯入匯出Oracle
- ORACLE exp/imp匯入報錯IMP-00009&IMP-00028&IMP-00015Oracle
- exp/imp工具
- Oracle中exp,imp(匯入匯出)資料遷移注意事項Oracle
- exp/imp工具的使用
- EXP COMPRESS以及EXP/IMP EXPDP/IMPDP匯入表結構注意
- exp/imp之@遠端匯出操作
- exp/imp匯入匯出版本問題和ORA-6550報錯
- Windows DOS窗體下Oracle 資料庫的匯入匯出(IMP/EXP)命令WindowsOracle資料庫
- oracle exp和impOracle
- oracle imp和expOracle
- EXP直接匯出壓縮問津，IMP直接匯入壓縮檔案的方法
- exp和imp詳解
- IMP和EXP筆記筆記
- Oracle imp/expOracle
- 【原創】比較資料泵和exp/imp對相同資料匯出/匯入的效能差異
- 【exp/imp不同版本】Oracle不同版本的exp/imp使用注意事項Oracle
- oracle技術_使用exp/imp 匯入11g資料到9iOracle
- Oracle資料庫備份與恢復之匯出／匯入（EXP/IMP）、熱備份和冷備份Oracle資料庫
- ORACLE IMP和EXP的使用實驗Oracle
- 【ORACLE 匯入匯出】exp 錯誤Oracle
- exp,imp相關工具說明
- 轉：Exp/Imp工具效能調優