exp和imp詳解

  之前寫了關於expdp和impdp的文章，感興趣的可以移步http://blog.itpub.net/30485601/viewspace-2151455/，由於expdp和impdp是oracle10g之後才有的，如果資料庫版本在10g之前，那麼就不能使用expdp和impdp了。但是我們卻可以使用exp和imp來實現邏輯匯入匯出功能，其實expdp和impdp本身就是在exp和imp的基礎上發展起來的，與之不同的是，exp和imp是一個客戶端工具，可以在客戶端進行操作，而且只需要resource許可權就可以了，所以對於日常的資料遷移還是很方便的。
  如果先看了expdp和impdp的話，本篇文章看起來就會覺得簡陋很多，因為兩個本身從實用性來說的話，當然長江後浪推前浪了，其實這要看和誰對比了，現在的大牛比兩年前的大牛厲害，但人家2年前依舊是一個大牛。那到底是用expdp呢，還是exp呢，其實和使用場景有關，如果你是一個系統管理員，擁有DBA許可權，那麼在資料遷移的時候，當然我覺得可以給自己分配一個擁有訪問系統目錄和expdp許可權的使用者，可以盡情體驗expdp帶來的快感；如果你只是一個普通使用者，是由別人分配給你的許可權，比如：你的資料庫伺服器是託管的，託管方一個庫上負載了多個庫，你的庫只是人家管理的其中一個，那麼對方一般情況下是不會給你一個高許可權的使用者的，你所擁有的只是connect和resource許可權，在這種情況下，你可以使用exp，關鍵是你也沒有許可權用expdp，對於日常的遷移備份，exp和imp絕對是綽綽有餘了。看了好一會兒文字，估計看客們心情都放鬆了吧，那老司機我就要發車了。
準備工作：如果需要在客戶端進行exp和imp的話，需要在客戶端下載完整版的客戶端，然後配置環境變數tnsnames.ora就可以了。
一、exp
1、匯出schemas
exp qj/**@local file=C:\Users\QuJian\Desktop\qj.dmp log=C:\Users\QuJian\Desktop\qj.log owner=qj statistics=none
解釋：exp和imp都是作業系統命令，在作業系統級別操作。exp表示這是一個exp匯出操作；qj/**，其中qj是使用者名稱，**是使用者密碼，對於qj這個使用者，如果只有resource許可權，那麼只能匯出qj自己，如果具有exp_full_database,那麼將會擁有匯出整個庫的許可權，當然也可以匯出其他schemas了；@local，local是tnsmames(網路服務名)，需要在客戶端配置環境變數TNS_NAME，可以透過路徑找到這個tnsnames，tnsnames記錄了資料庫伺服器的地址和服務名,透過local就可以找到資料庫伺服器的地址；file表示資料匯出的位置，匯出檔案以.dmp字尾，log表示匯出日誌,匯出日誌以.log字尾;owner指明要匯出的schema，如果具有exp_full_database許可權的話，那麼還可以匯出其他schema，多個schema用逗號(,)隔開，比如：owner=(qj,scott);由於客戶端字符集和伺服器字符集不一致，因此會出現EXP-00091 正在匯出有問題的統計資訊這個錯誤，加上statistics=none可以解決這個錯誤。下面貼出正常匯出的過程和沒有加statistics=none匯出過程：
如圖：
正常匯出的過程


對於exp-00104錯誤，目前只能透過expdp來解決
沒有加statistics=none匯出如圖：

可以看到，出現了大量的exp-00091錯誤。
2、匯出某些表,我將以匯出表為例，列出一些常用的引數
<1>tables
exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none
解釋：tables引數指明匯出哪些表，多個表之間用逗號(,)分隔
結果如圖：

<2>query
exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2)  query=\"where object_id<1\" statistics=none
解釋：query引數可以匯出指定條件的記錄，但是這裡有一點需要注意，expdp的query引數如果只指定了一個表的條件，其他表沒有指定條件，意味著其他表全導。但是對於exp，這個query滿足的欄位必須是所有表都有的，如果在這裡直接寫t1.object_id<1就會報錯，所以，where指出的條件，必須是要匯出的表都有的，要不然只能分類匯出表了。
結果如圖：

<3>parfile
exp qj/**@local parfile=C:\Users\QuJian\Desktop\par.txt
解釋：parfile可以直接呼叫命令檔案，可以看到<2>中，加query時還需要跳脫字元，這個簡直是太麻煩了，而且命令過長，超出了介面所能容納的上限還會報錯，主要是還不美觀。如果加了parfile引數，那麼我們只需要在命令列寫出exp qj/**@local parfile直接呼叫寫好的命令就可以了。
pafile文字如下：
file=C:\Users\QuJian\Desktop\par.dmp
log=C:\Users\QuJian\Desktop\par.log
tables=(t1,t2)  
query="where object_id<1"
statistics=none
結果如圖：

<4>grants,indexes,constraints,trigger
exp qj/**@local file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none grants=n
解釋：grants指定是否匯出物件的授權資訊，授權資訊指的是透過grant命令授予的物件訪問，操作許可權，預設為Y，也就是預設匯出；indexes指定是否匯出表的索引，預設為Y；constraints指定是否匯出表的約束，預設為Y;triggers是否匯出與表相關的觸發器，預設為Y。
<5>filesize
exp qj/**@local filesize=10m file=C:\Users\QuJian\Desktop\qj1.dmp,C:\Users\QuJian\Desktop\qj2.dmp  owner=qj statistics=none
解釋：filesize可以分割匯出的.dmp檔案，這裡我將每一個.dmp檔案設定為10m，超出10m就自動匯入到下一個檔案，為了使匯出檔案夠大，在這裡我匯出了qj這個schema
結果如圖：

<6>buffer
exp qj/**@local  file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none buffer=6400000
解釋：buffer可以用來設定資料行緩衝區大小，適當設定可以提高匯出的速度。buffer的大小是這麼計算的，比如t1這個表有5個欄位，2個number(2)，3個varchar2(20),一個number(2)佔2個位元組，一個varchar2(20)佔20個位元組，那麼一行佔2*2+20*3=64位元組，我一共匯出了10W行，那麼buffer=64*100000=6400000，6400000/1024/1024=6M
<7>direct和recordlength
exp qj/**@local  file=C:\Users\QuJian\Desktop\t.dmp log=C:\Users\QuJian\Desktop\t.log tables=(t1,t2) statistics=none direct=y recordlength=6400000
解釋：direct採用了直接路徑的方式，一般和recordlength引數配合使用，常規路徑匯出使用SQL SELECT語句從表中抽取資料，直接路徑匯出則是將資料直接從磁碟讀到PGA再原樣寫入匯出檔案，從而避免了SQL命令處理層的資料轉換過程，大大提高了匯出效率，在資料量大的情況下，直接路徑匯出的效率優勢更為明顯，recordlength指定了I/O緩衝區的大小，和buffer類似。
3、匯出表空間
exp qj/***@local file=C:\Users\QuJian\Desktop\ceshi.dmp log=C:\Users\QuJian\Desktop\ceshi.log tablespaces=users statistics=none
解釋： tablespaces指定要匯出的表空間，多個表空間中間用逗號(,)隔開，匯出表空間需要有exp_full_database許可權。
結果如圖：

4、匯出全庫
exp sys/**@local file=C:\Users\QuJian\Desktop\full.dmp log=C:\Users\QuJian\Desktop\full.log full=y statistics=none
解釋：full=y指明匯出全庫，匯出全庫的使用者必須擁有exp_full_database的許可權，在這我使用sys使用者匯出，用sys特權使用者匯出的，必須由sys特權使用者匯入。
結果如圖：





5、rows=n,表示只匯出表結構
exp qj/**@local file=C:\Users\QuJian\Desktop\t1.dmp log=C:\Users\QuJian\Desktop\t1.log  statistics=none rows=n
更多引數，請參照exp help=y
二、imp
1、全部匯入
不論匯出的是schema,table,還是tablespace或者整個庫，如果全部匯入的話，都可以同等處理，當然，匯入tablespace和全庫需要有imp_full_database許可權。
imp qj/***@local file=C:\Users\QuJian\Desktop\ceshi.dmp log=C:\Users\QuJian\Desktop\ceshi.log fromuser=ceshi touser=qj
解釋：imp表明這是一個匯入操作，file要匯入的原始檔路徑，log匯入日誌生成的路徑，fromuser從哪個使用者匯出的資料，touser匯入到哪個使用者，如果不知道原始檔案是從哪個使用者匯出來的，可以直接fromuser=（這裡不寫來源使用者）,匯入操作會報錯，這個報錯就會提示源資料是從哪個使用者匯出來的，然後再次執行匯入操作就可以了。
結果如圖：

2、ignore引數
如果即將匯入的庫中已經有了此表，或者此表的表結構發生了變化(比如由char變成了varchar2)，那麼匯入就會報如圖錯誤：

這個時候，只要加上ignore=y就可以了，
語句如下：imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi ignore=y
結果如圖：

3、tables引數，full引數，parfile引數
imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi tables=(demo)
imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi full=y
imp qj/***@local  parfile=C:\Users\QuJian\Desktop\par.txt
更多imp引數，可以透過imp help=y查詢；
4、分割槽表的匯入，index引數
如果匯出資料中有分割槽表，那麼在目標庫沒有相同DDL建表語句的時候就會報錯，解決報錯有兩種方法：
(1)在匯入資料庫中，與被匯入表的分割槽相關的表空間已對等建立好（表空間名也是與exp資料庫中一致的），那麼這種情況，與普通表的imp操作是一樣的;
(2)但多數情況下，我們不知道exp資料庫中該表的物理結構，此時，可以執行以下步驟:
<1>從源目標表中獲取分割槽表的DDL
語句如下：
imp qj/***@local  file=C:\Users\QuJian\Desktop\demo.dmp log=C:\Users\QuJian\Desktop\demo.log fromuser=qj touser=ceshi tables=(t1) index=C:\Users\QuJian\Desktop\t1.sql ignore=y
這樣一來，就將源表的DDL匯出到C:\Users\QuJian\Desktop\t1.sql，接下來執行第二步
<2>編輯這個分割槽表的DDL，如果希望和源庫的DDL一致，那麼就在目標庫建立相同名稱的表空間，之後執行這個DDL語句在目標庫先建立分割槽表，然後執行匯入操作(加ignore=y)；如果希望用目標庫的表空間名，那麼將從源庫匯出的分割槽表DDL中的表空間名編輯成目標庫的表空間，執行DDL在目標庫先建立分割槽表，然後執行匯入操作(加ignore=y)。
三、
exp和imp引數眾多，不過我們也完全不用記那麼多，只要知道怎麼用就可以了，具體的可以exp/imp help=y，你就會知道怎麼用的，備份作為處理資料問題最重要的手段，exp給我們提供了巨大的幫助，還是值得好好掌握的。學習oracle的路上是不孤獨的，因為你有著眾多的同行者，歡迎大家加入下面的資料庫討論群，讓我們一起互助前行。