Oracle Exp/Imp常見問題

Oracle Exp/Imp 常見問題

 

一 使用 EXP/IMP 前應該考慮的因素：

EXP

1

如果透過 DBCA 介面方式建立的資料庫，那麼 DBCA 會自動建立執行 IMP/EXP 所需的 檢視 和 角色 。

如果資料庫是透過 手動建立 ( create database*** ) ，那麼在執行 IMP/EXP 之前，必須首先執行 cataxp.sql 或 catalog.sql ( 包含呼叫 cataexp.sql 的語句 ) 。

cataxp.sql 指令碼檔案中主要執行以下任務：

1) 建立執行IMPORT/EXP 所需的資料字典及相關檢視；

2) 建立EXP_FULL_DATABASE 角色並授予相關許可權，擁有該角色的使用者能夠執行整庫的匯出；

3) 建立IMP_FULL_DATABASE 角色並授予相關許可權，擁有該角色的使用者能夠執行整庫的匯入；

4) 將EXP_FULL_DATABASE/IMP_FULL_DATABASE 兩個角色授予DBA ，注意這個DBA 不能指操作作者本人，而是Oracle 資料庫中的角色，也就是管理員角色。

 

2 授予許可權：

執行 IMP/EXP 的使用者至少要有 CREATE SESSION 許可權，即連線資料庫的許可權，該許可權包含在 CONNECT 角色中。

預設情況下，使用者只能匯出自己的表，要匯出其他 SCHEMA 擁有的表，執行匯出的使用者還必須擁有 EXP_FULL_DATABASE 角色，如果匯入其他 SCHEMA ，執行匯入使用者必須擁有 IMP_FULL_DATABASE 角色。

grant create session to scott;

grant exp_full_database to scott;

grant imp_full_database to scott;

 

IMP

EXP 命令是向 Dump 檔案中寫資料，而 IMP 命令則是從 Dump 檔案中讀資料。

匯入之前應該考慮的因素：

1) 生成DUMP 檔案的資料庫版本，如果比目標伺服器資料庫版本高，那麼匯入可能失敗；

2) 生成DUMP 檔案的使用者及該使用者擁有的角色，如果匯出時是DBA 使用者，或擁有EXP_FULL_DATABASE 角色的使用者執行的全庫匯出，那麼匯入時的使用者也必須要擁有相應的許可權；

3) 匯出的資料庫中是否含有 LOB 型別，是否有 分割槽表，分割槽索引，外部表 等，如果有，可能在匯入前也需要做一些準備工作；

4) 源資料預設表空間和目標資料預設表空間是否相同；

 

二 EXP/IMP 的呼叫方式：

EXP

1) 互動式

exp help=y

2) 引數檔案方式

IMP/EXP 命令都支援 PARFILE 引數，該引數的作用是指定一個引數檔案。指定的引數檔案是一個文字格式的檔案

例如：

vim parameter.dat

file=/home/oracle/scott.dmp

indexes=n

buffer=20480000

......

exp scott/tiger parfile=parameter.dat

當引數過多，或字串過長等適合用交引數檔案方式。

當引數檔案和命令引數有衝突時，一般以最後讀到的引數為準。

3) 命令列方式

 

IMP

1) 互動式

2) 引數檔案方式

3) 命令列方式

 

三 EXP 處理模式

EXP

1) 表模式

exp scott/tiger file=a.dmp log=a.log tables=emp compress=n

exp scott/tiger file=a.dmp log=a.log tables=(emp,dept) compress=n

exp scott/tiger file=a.dmp log=a.log tables=emp query="'where deptno=20 and ename!="SCOTT"'" compress=n

exp scott/tiger parfile=parameter.txt compress=n

vim parameter.txt

tables=emp

query='where deptno=20 and ename!="SCOTT"'

2) 使用者模式

exp scott/tiger file=a.dmp log=a.log owner=scott compress=n

exp scott/tiger file a.dmp log=a.log owner=(scott,chen) compress=n

3) 整庫模式

exp scott/tiger file=full.dmp log=full.log full=y

只是邏輯上的全庫，只匯出了和使用者資料相關的物件。

4) 表空間模式

 

IMP

1) 表模式

2) 使用者模式

3) 整庫模式

4) 表空間模式

 

 

四 EXP 常見問題

影響 EXP/IMP 匯出匯入的因素：

1)compress=N

在執行 exp 匯出的時候，如果不指定 compress=N 會遇到的問題 ( 預設初始值是 Y) ，那麼 exp 會嘗試壓縮表的 extent ：根據表中所有的 extent 的大小建立一個很大的初始 extent ，將表的全部 extent 壓縮到一個extent 中。如果利用這樣匯出的 dmp 檔案再匯入到目標庫的話，目標庫建立的對應的表的初始 extent 將會非常大，這樣即使 truncate 這張表後，保留的使用的空間依然會很大。

2)EXP-00091: Exporting questionable statistics.

出現場景： EXP-00091 錯誤常在 crontab 執行指令碼時出現；

問題原因： 執行匯出命令的客戶端 NLS_LANG 引數未設定或設定不合理；

解決方法：

查詢目標資料庫的字符集

select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

然後在執行 exp 命令的客戶端設定 NLS_LANG 變數；

3)ORA-00904

執行 EXP 命令的客戶端資料庫版本不能高於伺服器端資料庫版本；否則含 LOB 自動的資料在匯出時可能會失敗；

4) 執行匯出莫名停頓，但 dump 檔案大小瘋漲

原因： 匯出外部表時，如果外部表是透過 ORACLE_LOADER 方式建立的，那麼在匯出時只導定義，不導資料；

如果外部表時透過 ORACLE_DATAPUMP(10g) 匯出，不僅不匯出資料，匯出表定義會觸發 BUG ，使得 DUMP 瘋漲， 10.2.0.3 以後已經修改了 BUG 。

5) 匯出檔案過大，超出檔案系統限制怎麼辦？

不同的檔案系統，支援的單個檔案最大容量不同。

例如：

FAT16 ：單個檔案不超過 2GB

FAT32 ：單個檔案不超過 4GB

NTFS ：單個檔案不超過 2TB

Ext2 、 Ext3 ：單個檔案不超過 2TB

透過引數 FILESIZE 可以限制單個檔案大小 , 預設單位為位元組

exp scott/tiger filesize=100M file=scott.dmp log=scott.dmp owner=scott compress=n

SQL> select sum(bytes) from user_segments;

SQL> select owner,sum(bytes) from dba_segments where owner in(CHEN,SCOTT) group by owner order by 2 desc;

 

6) 試試能否更快的匯出？

匯出方式：

1) 常規路徑 (Conventional Path)

2) 直接路徑 (Direct Path)

常規路徑 (Conventional Path) 匯出是 EXP 預設的匯出方式 ，在這種方式下， EXP 要處理的資料需要經過 SQL SELECT 語句的方式提取，將資料讀取到緩衝池，經由 Evaluatin Buffer 處理後返回到 Export 客戶端，最後被匯入到 DUMP 檔案。

要提供處理速度，最重要的引數是 BUFFER 。該引數用於指定執行匯出時，處理資料所用的緩衝區的大小，以位元組為單位。這個引數變相用來控制匯出時記錄陣列單次最大能載入的記錄數。

緩衝區大小與載入記錄數之間可以透過下列公式換算：

緩衝區大小 = 記錄陣列大小×記錄行最大值

例如：

buffer 預設是4096 位元組

當匯出大表時，可以加大 buffer, 提高匯出速度

exp chen/chen file=a.dmp log=a.dmp owner=chen buffer=5120000 compress=n

 

直接路徑 (Direct Path)

直接路徑 (Direct Path) 也需要先把資料載入到資料庫緩衝池，不過不需要再經過 Evaluatin Buffer 處理，而是直接返回到 Export 客戶端並寫入指定的 DUMP 檔案。

需要在執行 exp 命令時指定引數 DIRECT 值為Y ( 預設值為 N) ；

影響直接路徑匯出效率的另一個引數是 : RECORDLENGTH ，該引數用來指定 Export 的 I/O Buffer ，以位元組為單位，最大不超過 65535 。

例如：

exp test/test file=a.dmp log=a.dmp owner=test direct=Y recordlength=65535 compress=n

 

五 IMP 常見問題

問題一：

將 test 使用者下的表，匯入到 chen 使用者下

其中 test 使用者預設表空間 test_tab ,chen 使用者預設表空間 chen_tab

imp chen/chen file=test.dmp log=test.log fromuser=test touser=chen

由於匯入時，會將 scott.dmp 的儲存定義 也匯入到 chen 使用者下，即匯入後 chen 使用者下的表預設表空間並不是 chen_tab ，而是 test_tab;

其中：

如果 chen 所在資料庫中有 test_tab 表空間，那麼可以正常匯入；如果沒有，匯入時會報錯並且終止；

問題原因： 由於 RESOURCE 角色包含 unlimited tablespace 許可權，該許可權的作用是允許使用者擁有 無限操作表空間儲存的許可權 。這可能導致在 imp 匯入資料庫時使得資料儲存在了預想以外的表空間。

解決辦法：

1) 顯式的授予使用者指定表空間的儲存許可權，希望使用者操作哪個表空間，就授予哪個表空間的操作許可權；

SQL> alter user chen quota unlimited on chen_tab;

2) 然後回收 Unlimited Tablespace 許可權

SQL> revoke unlimited tablespace from chen;

這時在將 test 使用者下的表匯入到 chen 使用者下時，所使用的表空間就可以是 chen_tab 了，但是前提必須加引數 IGNORE( 忽略錯誤 ) ，否則會終止匯入；

imp chen/chen file=test.dmp log=test.log fromuser=test touser=chen ignore=y

 

問題二：

匯入含 LOB 型別的表，且表空間和當前使用者預設表空間不同

如果匯入的 使用者預設表空間 與資料的 原儲存表空間不同 ，並且表中含有 LOB 型別 或分割槽表、分割槽索引之類的物件。這種型別的匯入，處理將會更復雜，甚至無法直接使用 IGNORE 引數解決問題。

可以根據錯誤提示 手動建立表定義 ， 設定適合的儲存引數 ；

 

問題三：

SEQUENCE 序列未變

如果匯入的 SEQUENCE 在目標端不存在，那麼不會有問題；

如果在目標端已經存在同名的 SEQUENCE 物件 ，並且在 IMP 匯入時指定 IGNORE=Y 引數，那麼匯入可以順利完成，不過匯入的結果並不是預期想要的。

IMP 匯入時指定 IGNORE=Y 引數，對於已經存在的物件會忽略建立語句導致的錯誤，問題在於 SEQUENCE 物件的屬性恰恰是 CREATE SEQUENCE 時指定的 (IMP 並不會將建立語句轉換成修改屬性語句 ) ，看起來 SEQUENCE 物件匯入成功，但其實 IMP 什麼也沒做。

解決辦法：匯入之前，刪除同名的 SEQUENCE 物件；

 

匯入速度的提升：

BUFFER

imp chen/chen file=scott.dmp log=scott.log fromuser=scott touser=chen buffer=40960000

 

六 EXP 常用引數：

1 file

2 log

3 owner

4 tables

5 full

6 buffer

7 compress

8 GRANTS: 指定是否匯出物件的授權資訊，預設引數值為 Y ，即預設匯出；

9 INDEXES: 指是否匯出表的所有，預設為 Y ；

10 CONSTRAINTS: 指是否匯出表的約束，預設值 Y ；

11 TRIGGERS: 指是否匯出與表相關的觸發器，預設值 Y ；  

12 direct

13 recordlength

 

七 IMP 常用引數

1 file

2 log

3 fromuser

4 touser

5 ignore

6 buffer

 

八 IMP 在匯入表物件時的順序與 EXP 匯出表物件的順序相同：

1) 匯入表定義，即建立表物件；

2) 匯入表資料；

3) 匯入索引資料；

4) 建立完整性約束、檢視、過程及觸發器；

5) 匯入 Bitmap 、 Function-Based 以及 Domain 等型別索引；

 

九 物件型別：

聚簇分析，統計資訊，應用程式上下文，審計資訊，聚簇定義，資料庫鏈，預設角色，維度， Directory ，外部表 ( 不含資料 ) ，索引型別， Java Resources and classes ， JOB 佇列，巢狀表序列，物件的授權，用於表的物件型別定義，物件型別的定義，使用者定義的同義詞，使用者概要檔案 (profiles) ，公共同義詞，外來鍵約束，角色及授權，回滾段定義，序列，物化檢視、物化檢視日誌及重新整理組，授予的系統許可權，表結構、表資料、表索引、約束、觸發器及表和列的註釋，表空間的定義，表空間使用限額，使用者定義，使用者代理，使用者檢視，使用者定義的函式、過程、包；

 

 

參考：《塗抹 Oracle 》

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！