IMP和EXP筆記

操作的都是二進位制檔案 檢視幫助([oracle@oracle oracle]$ exp help=y)
好處：1、資料庫遷移
            2、歷史庫歸檔
            3、重新組織表，處理行的遷移，高水位線的處理
            4、轉移資料
            5、物理備份的一種輔助備份

   
EXP的所有引數（括號中為引數的預設值）：
USERID        使用者名稱/口令      如： USERID=duanl/duanl     
FULL          匯出整個資料庫 (N)
BUFFER        資料緩衝區的大小        
OWNER        所有者使用者名稱列表,你希望匯出哪個使用者的物件，就用owner=username
FILE           輸出檔案 (EXPDAT.DMP)   
TABLES        表名列表 ,指定匯出的table名稱，如：TABLES=table1,table2
COMPRESS     匯入一個extent (Y)  
RECORDLENGTH   IO 記錄的長度
GRANTS        匯出許可權 (Y)           
INCTYPE        增量匯出型別
INDEXES        匯出索引 (Y)          
RECORD        跟蹤增量匯出 (Y)
ROWS           匯出資料行 (Y)       
PARFILE        引數檔名,如果你exp的引數很多，可以存成引數檔案.
CONSTRAINTS   匯出約束 (Y)   
CONSISTENT    交叉表一致性
LOG            螢幕輸出的日誌檔案   
STATISTICS     分析物件 (ESTIMATE)
DIRECT         直接路徑 (N)             
TRIGGERS      匯出觸發器 (Y)
FEEDBACK      顯示每 x 行 (0) 的進度
FILESIZE        各轉儲檔案的最大尺寸
QUERY          選定匯出表子集的子句
下列關鍵字僅用於可傳輸的表空間
TRANSPORT_TABLESPACE 匯出可傳輸的表空間後設資料 (N)
TABLESPACES 將傳輸的表空間列表
DATAFILES 將要傳輸到資料庫的資料檔案
TTS_OWNERS 擁有可傳輸表空間集中資料的使用者
關於增量引數的說明：exp/imp的增量並不是真正意義上的增量，所以最好不要使用。
使用方法：
Exp parameter_name=value or Exp parameter_name=(value1,value2……)
只要輸入引數help=y就可以看到所有幫助.

不好理解的幾個引數說明：

BUFFER引數說明
Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.
Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:
buffer_size = rows_in_array * maximum_row_size

If you specify zero, the Export utility fetches only one row at a time.
Tables with columns of type LOBs, LONG, BFILE, REF, ROWID, LOGICAL ROWID, or DATE are fetched one row at a time.
Note:
The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export. For direct path Exports, use the RECORDLENGTH parameter to specify the size of the buffer that Export uses for writing to the export file.Example: Calculating Buffer Size
This section shows an example of how to calculate buffer size.
The following table is created:
CREATE TABLE sample (name varchar(30), weight number);

The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore, the maximum row size is 56 (30+2+22+2).
To perform. array operations for 100 rows, a buffer size of 5600 should be specified.

compress引數說明：
http://warehouse.itpub.net/post/777/519801

引數consistent說明：
http://warehouse.itpub.net/post/777/492413
consistent=N表的一致性讀取
consistent=Y被匯出的所有表的一致性讀取

RESUMABLE引數說明：用於在執行匯出時，目標磁碟不夠，語句自動掛起。
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE

有一個例子：
一個不能自動增長的表空間在插入資料時，可以為這個使用者（DBA角色才有這個許可權resumable）
許可權問題
select * from dba_resumable; 檢視掛住的時間等資訊
select * from system_privilege_map where name like 'resumable'; 檢視resumable 許可權
select * from dba_role_privs where grantee='TEST'   檢視TEST使用者的許可權
SELECT * from dba_sys_privs where grantee='DBA' and privilege='resumable'; 檢視DBA使用者和具有RESUMABLE許可權的使用者

掛住的的語句就需要手工干預，才能繼續執行。
DIRECT=Y  匯出資料較快，特別是匯出大資料時

 

EXP常用選項：
1.FULL，這個用於匯出整個資料庫，在ROWS=N一起使用時，可以匯出整個資料庫的結構。例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y
2. OWNER和TABLE，這兩個選項用於定義EXP的物件。OWNER定義匯出指定使用者的物件；TABLE指定EXP的table名稱，例如：
exp userid=test/test file=./db_str.dmp log=./db_str.log wner=duanl
exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap
3.BUFFER和FEEDBACK，在匯出比較多的資料時，我會考慮設定這兩個引數。例如：
exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT
4.FILE和LOG，這兩個引數分別指定備份的DMP名稱和LOG名稱，包括檔名和目錄，例子見上面。
5.COMPRESS引數不壓縮匯出資料的內容。用來控制匯出物件的storage語句如何產生。預設值為Y，使用預設值，物件的儲存語句的init extent等於當前匯出物件的extent的總和。推薦使用COMPRESS＝N。
6. FILESIZE該選項在8i中可用。如果匯出的dmp檔案過大時，最好使用FILESIZE引數，限制檔案大小不要超過2G。如：
exp userid=duanl/duanl file=f1,f2,f3,f4,f5 filesize=2G wner=scott
這樣將建立f1.dmp, f2.dmp等一系列檔案，每個大小都為2G，如果匯出的總量小於10G
EXP不必建立f5.bmp.

 

 2、IMP:

       具有三種模式（完全、使用者、表）

       1、完全：
          IMP SYSTEM/MANAGER  BUFFER=64000  FILE=C:\FULL.DMP  FULL=Y

       2、使用者模式：
          IMP  SONIC/SONIC     BUFFER=64000  FILE=C:\SONIC.DMP  FROMUSER=SONIC  TOUSER=SONIC
          這樣使用者SONIC的所有物件被匯入到檔案中。必須指定FROMUSER、TOUSER引數，這樣才能匯入資料。

       3、表模式：
          EXP  SONIC/SONIC     BUFFER=64000  FILE=C:\SONIC.DMP  WNER=SONIC TABLES=(SONIC)
          這樣使用者SONIC的表SONIC就被匯入。

imp引數：
關鍵字   說明（預設）  
———————————————-
USERID   使用者名稱/口令
FULL   匯入整個檔案 (N)
BUFFER   資料緩衝區大小
FROMUSER  所有人使用者名稱列表
FILE   輸入檔案 (EXPDAT.DMP)
TOUSER   使用者名稱列表
SHOW   只列出檔案內容 (N)
TABLES   表名列表
IGNORE   忽略建立錯誤 (N)
RECORDLENGTH  IO 記錄的長度
GRANTS   匯入許可權 (Y)
INCTYPE   增量匯入型別
INDEXES   匯入索引 (Y)
COMMIT   提交陣列插入 (N)
ROWS   匯入資料行 (Y)
PARFILE   引數檔名
LOG   螢幕輸出的日誌檔案
CONSTRAINTS  匯入限制 (Y)
DESTROY   覆蓋表空間資料檔案 (N)
INDEXFILE  將表/索引資訊寫入指定的檔案
SKIP_UNUSABLE_INDEXES 跳過不可用索引的維護 (N)
ANALYZE   執行轉儲檔案中的 ANALYZE 語句 (Y)
FEEDBACK  顯示每 x 行 (0) 的進度
TOID_NOVALIDATE  跳過指定型別 id 的校驗
FILESIZE  各轉儲檔案的最大尺寸
RECALCULATE_STATISTICS 重新計算統計值 (N)

IMP常用選項:
1、FROMUSER和TOUSER，使用它們實現將資料從一個SCHEMA中匯入到另外一個SCHEMA中。例如：假設我們做exp時匯出的為test的物件,現在我們想把物件匯入使用者：
imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1
2、IGNORE、GRANTS和INDEXES，其中IGNORE引數將忽略表的存在，繼續匯入，這個對於需要調整表的儲存引數時很有用，我們可以先根據實際情況用合理的儲存引數建好表，然後直接匯入資料。而GRANTS和INDEXES則表示是否匯入授權和索引，如果想使用新的儲存引數重建索引，或者為了加快到入速度，我們可以考慮將INDEXES設為N，而GRANTS一般都是Y。例如：imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N

表空間傳輸
     表空間傳輸是8i新增加的一種快速在資料庫間移動資料的一種辦法，是把一個資料庫上的格式資料檔案附加到另外一個資料庫中，而不是把資料匯出成Dmp檔案，這在有些時候是非常管用的，因為傳輸表空間移動資料就象複製檔案一樣快。
關於傳輸表空間有一些規則，即：
•源資料庫和目標資料庫必須執行在相同的硬體平臺上。
•源資料庫與目標資料庫必須使用相同的字符集。
•源資料庫與目標資料庫一定要有相同大小的資料塊
•目標資料庫不能有與遷移表空間同名的表空間
•SYS的物件不能遷移
•必須傳輸自包含的物件集
•有一些物件，如物化檢視，基於函式的索引等不能被傳輸

可以用以下的方法來檢測一個表空間或一套表空間是否符合傳輸標準：
exec sys.dbms_tts.transport_set_check(‘tablespace_name’,true);
select * from sys.transport_set_violation;
如果沒有行選擇，表示該表空間只包含表資料，並且是自包含的。對於有些非自包含的表空間，如資料表空間和索引表空間，可以一起傳輸。
以下為簡要使用步驟，如果想參考詳細使用方法，也可以參考ORACLE聯機幫助。
1.設定表空間為只讀（假定表空間名字為APP_Data 和APP_Index）
alter tablespace app_data read only;
alter tablespace app_index read only;
2.發出EXP命令
SQL>host exp userid=”””sys/password as sysdba”””
transport_tablespace=y tablespace=(app_data, app_index)
以上需要注意的是
•為了在SQL中執行EXP，USERID必須用三個引號，在UNIX中也必須注意避免“/”的使用
•在816和以後，必須使用sysdba才能操作
•這個命令在SQL中必須放置在一行（這裡是因為顯示問題放在了兩行）
3.複製資料檔案到另一個地點，即目標資料庫
   可以是cp(unix)或copy(windows)或透過ftp傳輸檔案（一定要在bin方式）
4.把本地的表空間設定為讀寫
5.在目標資料庫附加該資料檔案
imp file=expdat.dmp userid=”””sys/password as sysdba””” transport_tablespace=y “datafile=(c:\temp\app_data,c:\temp\app_index)”
6.設定目標資料庫表空間為讀寫
alter tablespace app_data read write;
alter tablespace app_index read write;

最佳化EXP/IMP的方法：
     當需要exp/imp的資料量比較大時，這個過程需要的時間是比較長的，我們可以用一些方法來最佳化exp/imp的操作。
exp:使用直接路徑 direct=y
oracle會避開sql語句處理引擎,直接從資料庫檔案中讀取資料,然後寫入匯出檔案.
可以在匯出日誌中觀察到: exp-00067: table xxx will be exported in conventional path
     如果沒有使用直接路徑,必須保證buffer引數的值足夠大.
     有一些引數於direct=y不相容,無法用直接路徑匯出可移動的tablespace,或者用query引數匯出資料庫子集.
     當匯入匯出的資料庫執行在不同的os下時,必須保證recordlength引數的值一致.

imp:透過以下幾個途徑最佳化
1.避免磁碟排序
將sort_area_size設定為一個較大的值,比如100M
2.避免日誌切換等待
增加重做日誌組的數量,增大日誌檔案大小.
3.最佳化日誌緩衝區
比如將log_buffer容量擴大10倍(最大不要超過5M)
4.使用陣列插入與提交
commit = y
注意:陣列方式不能處理包含LOB和LONG型別的表,對於這樣的table,如果使用commit = y,每插入一行,就會執行一次提交.
5.使用NOLOGGING方式減小重做日誌大小
在匯入時指定引數indexes=n,只匯入資料而忽略index,在導完資料後在透過指令碼建立index,指定 NOLOGGING選項
6、匯出/匯入與字符集
     進行資料的匯入匯出時，我們要注意關於字符集的問題。在EXP/IMP過程中我們需要注意四個字符集的引數：匯出端的客戶端字符集，匯出端資料庫字符集，匯入端的客戶端字符集，匯入端資料庫字符集。
我們首先需要檢視這四個字符集引數。
檢視資料庫的字符集的資訊：
SQL> select * from nls_database_parameters;
PARAMETER                       VALUE
------------------------------ --------------------------------------------------------------------------------
NLS_LANGUAGE                    AMERICAN
NLS_TERRITORY                    AMERICA
NLS_CURRENCY                    $
NLS_ISO_CURRENCY                AMERICA
NLS_NUMERIC_CHARACTERS          .,
NLS_CHARACTERSET                ZHS16GBK
NLS_CALENDAR                    GREGORIAN
NLS_DATE_FORMAT                 DD-MON-RR
NLS_DATE_LANGUAGE               AMERICAN
NLS_SORT                          BINARY
NLS_TIME_FORMAT                 HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT            DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT              HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT         DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY               $
NLS_COMP                        BINARY
NLS_NCHAR_CHARACTERSET          ZHS16GBK
NLS_RDBMS_VERSION               8.1.7.4.1
NLS_CHARACTERSET：ZHS16GBK是當前資料庫的字符集。
我們再來檢視客戶端的字符集資訊：
客戶端字符集的引數NLS_LANG=_< territory >.
language：指定oracle訊息使用的語言，日期中日和月的顯示。
Territory：指定貨幣和數字的格式，地區和計算星期及日期的習慣。
Characterset：控制客戶端應用程式使用的字符集。通常設定或等於客戶端的內碼表。或者對於unicode應用設為UTF8。
在windows中，查詢和修改NLS_LANG可在登錄檔中進行：
HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOMExx\
xx指存在多個Oracle_HOME時的系統編號。
在unix中：
$ env|grep NLS_LANG
NLS_LANG=simplified chinese_china.ZHS16GBK
修改可用：
$ export NLS_LANG=AMERICAN_AMERICA.UTF8

通常在匯出時最好把客戶端字符集設定得和資料庫端相同。當進行資料匯入時，主要有以下兩種情況：
(1)     源資料庫和目標資料庫具有相同的字符集設定。
這時，只需設定匯出和匯入端的客戶端NLS_LANG等於資料庫字符集即可。
(2)     源資料庫和目標資料庫字符集不同。
     先將匯出端客戶端的NLS_LANG設定成和匯出端的資料庫字符集一致，匯出資料，然後將匯入端客戶端的NLS_LANG設定成和匯出端一致，匯入資料，這樣轉換隻發生在資料庫端，而且只發生一次。
     這種情況下，只有當匯入端資料庫字符集為匯出端資料庫字符集的嚴格超集時，資料才能完全導成功，否則，可能會有資料不一致或亂碼出現。

不同版本的EXP/IMP問題
（1）版本問題
     一般來說，從低版本匯入到高版本問題不大，麻煩的是將高版本的資料匯入到低版本中，在Oracle9i之前，不同版本Oracle之間的EXP/IMP可以透過下面的方法來解決：
1、在高版本資料庫上執行底版本的catexp.sql；
2、使用低版本的EXP來匯出高版本的資料；
3、使用低版本的IMP將資料庫匯入到低版本資料庫中；
4、在高版本資料庫上重新執行高版本的catexp.sql指令碼。
但在9i中，上面的方法並不能解決問題。如果直接使用低版本EXP/IMP會出現如下錯誤：
EXP-00008: orACLE error %lu encountered
orA-00904: invalid column name
這已經是一個公佈的BUG，需要等到Oracle10.0才能解決，BUG號為2261722，你可以到METALINK上去檢視有關此BUG的詳細資訊。
BUG歸BUG，我們的工作還是要做，在沒有Oracle的支援之前，我們就自己解決。在Oracle9i中執行下面的SQL重建exu81rls檢視即可。
Create or REPLACE view exu81rls
(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)
AS select u.name, o.name, r.pname, r.pfschma, r.ppname, r.pfname,
decode(bitand(r.stmt_type,1), 0,'', 'Select,')
|| decode(bitand(r.stmt_type,2), 0,'', 'Insert,')
|| decode(bitand(r.stmt_type,4), 0,'', 'Update,')
|| decode(bitand(r.stmt_type,8), 0,'', 'Delete,'),
r.check_opt, r.enable_flag,
DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)
from user$ u, obj$ o, rls$ r
where u.user# = o.owner#
and r.obj# = o.obj#
and (uid = 0 or
uid = o.owner# or
exists ( select * from session_roles where role='Select_CATALOG_ROLE')
)
/
grant select on sys.exu81rls to public;
/
可以跨版本的使用EXP/IMP，但必須正確地使用EXP和IMP的版本：
1、總是使用IMP的版本匹配資料庫的版本，如：要匯入到817中，使用817的IMP工具。
2、總是使用EXP的版本匹配兩個資料庫中最低的版本，如：從9201往817中匯入，則使用817版本的EXP工具。

(2) 資料庫物件有主外來鍵約束
    不符合主外來鍵約束時, 資料會匯入失敗
    解決辦法: 先匯入主表, 再匯入依存表
    disable目標匯入物件的主外來鍵約束, 匯入資料後, 再enable它們
    (3)  許可權不夠
    如果要把A使用者的資料匯入B使用者下, A使用者需要有imp_full_database許可權

(4)  匯入大表( 大於80M ) 時, 儲存分配失敗
    預設的EXP時, compress = Y, 也就是把所有的資料壓縮在一個資料塊上.
    匯入時, 如果不存在連續一個大資料塊, 則會匯入失敗.
    匯出80M以上的大表時, 記得compress= N, 則不會引起這種錯誤.

(5) imp和exp使用的字符集不同
    如果字符集不同, 匯入會失敗, 可以改變unix環境變數或者NT登錄檔裡NLS_LANG相關資訊.
    匯入完成後再改回來.

(6) imp和exp版本不能往上相容
    imp可以成功匯入低版本exp生成的檔案, 不能匯入高版本exp生成的檔案
    根據情況我們可以用
    $ imp
    說明: connect_string 是在/ORACLE_HOME/network/admin/tnsnames.ora
    定義的本地或者遠端資料庫的名稱
    注意事項:
    UNIX: /etc/hosts                  要定義本地或者遠端資料庫伺服器的主機名
    win98:    windows\hosts             和IP地址的對應關係

    win2000:  winnt\system32\drivers\etc\hosts

(7) 資料庫物件已經存在
    一般情況, 匯入資料前應該徹底刪除目標資料下的表, 序列, 函式/過程,觸發器等;
    資料庫物件已經存在, 按預設的imp引數, 則會匯入失敗
    如果用了引數ignore=y, 會把exp檔案內的資料內容匯入
    如果表有唯一關鍵字的約束條件, 不合條件將不被匯入
    如果表沒有唯一關鍵字的約束條件, 將引起記錄重複