教你如何成為Oracle 10g OCP - 第十九章 資料遷移

主要內容
1. exp/imp的原理及使用
2. 如何進行可傳輸表空間的操作，特別是進行跨平臺表空間傳輸
3. expdp/impdp的原理及使用
4. 如何使用外部表
5. 如何進行可傳輸資料庫的操作 

1. exp/imp

exp/imp 原理:  它是一個位於客戶端的程式(在DB Server及遠端客戶端都能操作) , 能跨平臺進行匯出匯入操作，
普通的exp出來的dmp檔案包括兩部分內容，一部分是檔案頭，還有一部分是表和其它物件的定義,如果是表 ，則
還包括二進位制的行資料(如果選擇rows=y)，這種格式與實際塊中儲存的行格式一致(可以透過特殊工具比如UltraEdit
檢視到，如下，當然這些語句前面還有很多的二進位制的字元)。 

基本可以看出，就是將建立Objects的指令碼匯出，資料部分採用insert into的方式進行記錄。 

"
CREATE TABLE "PF_TEMP_EXCEL" ("COL1" VARCHAR2(36), "COL2" VARCHAR2(36), "COL3" VARCHAR2(36), "COL4" VARCHAR2(36))  PCTFREE 10 PCTUSED 0

INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO

"PF_TEMP_EXCEL" ("COL1", "COL2", "COL3", "COL4") VALUES (:1, :2, :3, :4)

exp/imp 是跨平臺的資料遷移工具，因為它的匯出是一種邏輯模式，而不是物理的，也就是說它是以建立物件指令碼以及
SQL語句插入資料為儲存格式的，而不是block為單位來複制的(Standby就是以Block為單位來進行備份的)，所以和
平臺沒有關係。exp/imp可以作為資料備份的補充手段(邏輯備份)。  

備註： Oracle10g或以後可以使用expdp/impdp, 但是exp/imp還是可以使用。後面會詳細講到expdp/impdp.  

 

exp有四種模式：

A. 表模式
B. 使用者模式(Schema)
C. 資料庫模式(可以匯出除了使用者sys以外的資料庫裡的所有物件)
D. 可傳輸表空間模式(匯出某個指定的表空間中的所有物件) 

 

$ exp  help=y 可以得到幫助
C:\>exp help=y

 

 

 

匯出某個使用者(hrm)下指定的表(表模式),其中table所在使用者就是hrm,如果不是需要加入schema.

$ exp hrm/hrm file=emp_dept.dmp  tables=(employees,departments)  log=empdept.log

$ exp hrm/hrm file=emp_dept.dmp  tables=(oe.employees,oe.departments)  log=empdept.log
(其中要求hrm使用者有許可權匯出匯入，且hrm使用者有檢視oe使用者下這兩個表的許可權) 

匯出某個使用者下的所有物件(物件包括：table,procedure,index,grant,trigger,constraint等)
$ exp  sys/sysoracle file=hrm.dmp  wner=hrm   log=hrm.log 

 

 

匯出整個資料庫： 連線使用者必須有 exp_full_database的系統許可權

SQL> grant exp_full_database to  hrm ; 
$ exp  hrm/hrm  file=full_db.dmp  full=y  log=full_db.log 

 

 

匯出時的輸入引數可以使用一個檔案代替。

$vi emp.par

userid=hrm/hrm
file=emp.dmp
tables=(emp,dept)
log=emp.log 

執行匯出命令： $ exp  parfile=emp.par 

 

 

 

匯出有兩種方式：  A. 傳統路徑匯出 ， B. 直接路徑匯出

A. 傳統路徑匯出： exp採用SQL語句的方式把要匯出的表的資料檢索出來，透過網路將資料傳輸到客戶端，在客戶端生成
轉儲檔案，由於是SQL語句方式，就需要在shared pool中解析執行計劃，將資料快取到buffer cache中，然後傳遞到客戶
端，這是預設的匯出方式。

B. 直接路徑匯出： 採用加入引數 direct=y 來設定。這種exp不採用傳統的SQL讀取資料的方式，而是直接獲得資料塊，並
繞過shared pool 和buffer cache , 將資料塊裡面的資料抽取出來以後直接傳輸給客戶端上，這種方式相對傳統方式會快很多，但是這種方式有一些限制，比如

不能匯出含有 LOB,BFILE等大物件的表，還有含有Query子句的exp也不能使用direct=y  . 

傳統路徑匯出： 要經過SQL語句，shared pool, buffer cache等一系列SQL必須的過程，且資料會被移動到一個工作區，行被提出來，檢查和Query查詢中的

where子句(如果有的話)是否匹配。所以速度慢一些。

直接路徑匯出： Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer

(evaluating buffer). This method can be much faster than a conventional path Export. 

 

 

 
imp匯入同樣有四種模式 。
$ imp hr/hr file=emp.dmp table=(emp,dept) log=emp.log ignore=y
$ imp sys/sysora fromuser=hrm  touser=hrkq  file=emp.dmp table=(emp,dept) ignore=y  log=emp.log 
匯入時也可以使用引數檔案 parfile .

 

匯入物件的順序：

A. 建立表結構
B. 匯入表的資料
C. 建立索引
D. 匯入觸發器
E. 對匯入的表啟用完整性約束
F. 建立所有點陣圖，函式以及其他索引 

如果出現table已經存在等錯誤，那麼imp就不能正常進行了，我們可以使用ignore=y來解決。

 

 

exp/imp的注意事項：

1. Conventional path Export.
    -------------------------
傳統匯出模式使用SQL SELECT語句抽取表資料。將資料從磁碟中讀入到buffer cache緩衝區中，並應用SQL表示式，將紀錄返回給匯出客戶端，然後寫到到處檔案

。

2. Direct path Export.
    -------------------
直接匯出模式，資料直接從磁碟中讀取到匯出session的UGA中，跳過了SQL命令處理層。避免了不必要的資料轉換， 然後將紀錄返回給匯出客戶端，然後寫到到

處檔案   跳過了SQL命令處理層表示DIRECT匯出不支援QUERY選項。

效能
    1. Direct Export 比Conventional Export要快很多。在實際應用中，如果直接模式匯出需要時間N，傳統模式匯出則需要2*N到3*N之間。
    2. 當Direct Export 的時候設定大的RECORDLENGTH（length of IO record） 引數可以加快匯出。最大64k，
    exp userid=system/manager full=y direct=y recordlength=65535
    file=exp_full.dmp log=exp_full.log
    imp userid=system/manager full=y recordlength=65535
    file=exp_full.dmp log=imp_full.log
    3. Direct 模式匯出並不影響匯入資料的速度；匯入資料與傳統模式花費時間一樣。
    4. imp時適當調整引數buffer以及資料庫undo大小以及UNDO_RETENTION時間 。
    5. exp時候加入 consistent=n  

限制
    ------------
    1. 匯出表空間必須使用傳統模式。
    2. 含有LOB物件的表不支援直接匯出模式。
    3. 直接匯出不支援QUERY.
    4. 直接匯出模式使用RECORDLENGTH設定一次可以匯出資料的量，傳統模式使用buffer設定.
    5. 低版本直接匯出模式要求匯出客戶端和資料庫字符集設定一致。

    直接匯出模式bug比傳統模式要多，但由於其匯出資料在效能上的優勢，仍然要多加使用。

 

常用的一些exp/imp語句例子：

exp  "'sys/oracle  as sysdba'"  buffer=8192  tables=pdm626.streamdata  feedback=200  compress=n   filesize=20G  file=

(streamdata1,streamdata2)  log=stream    

exp  system/system  QUERY=\"WHERE streamid>=1 and streamid<2000\"   buffer=8192   tables=pdm626.streamdata   feedback=50  consistent=n 

compress=n    filesize=20G   log=pdm_streamdata  file=(pdmstream_1,pdmstream_2)     

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=cis  feedback=100  consistent=n compress=n   filesize=10G  log=cis 

imp  "'sys/oracle  as sysdba'"  fromuser=cis  touser=cis  file=cis_1.DMP  log=cisimp2  ignore=y 

 

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=pdmext  feedback=100  consistent=n  compress=n   filesize=10G  log=pdmext  

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=pdmext   feedback=100   consistent=n
compress=n    filesize=20G   log=pdmext_data  file=(pdmext_1,pdmext_2,pdmext_3,pdmext_4,pdmext_5)   

imp  "'sys/oracle  as sysdba'"  fromuser=pdmext  touser=pdmext  file=pdmext_1.DMP  log=pdmextimp_data  ignore=y

exp  system/system  QUERY=\"WHERE streamid>=100000 and streamid<150000\"   buffer=8192   tables=pdm626.streamdata   feedback=50  consistent=n  compress=n    filesize=20G   log=pdmstream3  file=(pdmstream3_1,pdmstream3_2)