oracle導入資料庫數據

① oracle 資料庫導入導出

Oracle ExpImp導入導出工具性能調優

Oracle Exp/Imp工具是一個操作簡單、方便靈活的備份恢復和數據遷移工具，它可以實施全庫級、用戶級、表級的數據備份和恢復。對於數據量在G級或G級以內，強調高可用性，可以容忍少量數據丟失的資料庫系統，Exp/Imp是普遍使用的邏輯備份方式。目前現網很多生產系統均使用Exp/Imp進行備份恢復。數據量達到G級以後，備份恢復的時間明顯拉長了，有沒有方法能夠有效提高Exp/Imp的速度呢？答案是肯定的，某些方法還可以成倍的提高速度，本文就從Exp、Imp兩個工具分別探討優化備份恢復性能的方法。

1 Exp調優

1.1 使用DIRECT和RECORDLENGTH選項

DIRECT參數定義了導出是使用直接路徑方式(DIRECT=Y)，還是常規路徑方式(DIRECT=N)。常規路徑導出使用sql SELECT語句從表中抽取數據，直接路徑導出則是將數據直接從磁碟讀到PGA再原樣寫入導出文件，從而避免了SQL命令處理層的數據轉換過程，大大提高了導出效率。在數據量大的情況下，直接路徑導出的效率優勢更為明顯，可比常規方法速度提高三倍之多。

和DIRECT=Y配合使用的是RECORDLENGTH參數，它定義了Export I/O緩沖的大小，作用類似於常規路徑導出使用的BUFFER參數。建議設置RECORDLENGTH參數為最大I/O緩沖，即65535(64kb)。其用法如下：

exp userid=system/manager full=y direct=y recordlength=65535 file=exp_full.dmp log=exp_full.log

直接路徑導出根據Oracle版本不同，有一些使用限制。比較重要的限制有，8i及以下版本不支持導出客戶端和資料庫的字元集轉換，因此導出前必須保證NLS_LANG設置正確;8.1.5及以下版本不支持導出含LOBs對象的表;不能使用QUERY參數等。

1.2 使用管道技術

管道是從一個程序進程向另一個程序進程單向傳送信息的技術。通常，管道把一個進程的輸出傳給另一進程作為輸入。如果導出的數據量很大，可以利用管道直接生成最終的壓縮文件，所耗費的時間和不壓縮直接導出的時間相當。這樣一來，不僅能夠解決磁碟空間不足的問題，而且省去了單獨壓縮文件的時間;如果需要傳輸導出文件，還可以減少網路傳輸的時間。比如，一個10G的文件單獨壓縮可能需要半小時以上的時間。雖然管道技術不能夠直接縮短Exp/Imp本身的時間，但節省出來的壓縮時間非常可觀。管道和Exp結合的具體使用方法如下：

導出數據示例：

% mknod /tmp/exp_pipe p # Make the pipe

% compress < /tmp/exp_pipe > export.dmp.Z & # Background compress

% exp file=/tmp/exp_pipe # Export to the pipe

2 Imp調優

Oracle Import進程需要花比Export進程數倍的時間將數據導入資料庫。某些關鍵時刻，導入是為了應對資料庫的緊急故障恢復。為了減少宕機時間，加快導入速度顯得至關重要。沒有特效辦法加速一個大數據量的導入，但我們可以做一些適當的設定以減少整個導入時間。

2.1 使用管道技術

前面已經說明了Exp時如何使用管道，在導入時管道的作用是相同，不僅能夠解決磁碟空間不足的問題，而且省去了單獨解壓縮文件的時間。在大數據量導入導出的時候，推薦一定要使用管道。

導入數據示例：

2.2 避免I/O競爭

Import是一個I/O密集的操作，避免I/O競爭可以加快導入速度。如果可能，不要在系統高峰的時間導入數據，不要在導入數據時運行job等可能競爭系統資源的操作。

2.3 增加排序區

Oracle Import進程先導入數據再創建索引，不論INDEXES值設為YES或者NO，主鍵的索引是一定會創建的。創建索引的時候需要用到排序區，在內存大小不足的時候，使用臨時表空間進行磁碟排序，由於磁碟排序效率和內存排序效率相差好幾個數量級。增加排序區可以大大提高創建索引的效率，從而加快導入速度。

8i及其以下版本：導入數據前增加資料庫的sort_area_size大小，可設為正常值的5-10倍。但這個值設定會影響到所有會話，設的過高有可能導致內存不足出現paging, swapping現象。更為穩妥的方法是，對於大表和索引特別多的表，只導數據不導索引。導完數據後，創建一個會話，設定當前會話的sort_area_size一個足夠大的值，再手工創建索引。

9i：在workarea_size_policy=AUTO的情況下，所有會話的UGA共用pga_aggregate_target定義的內存，不必單獨設定sort_area_size。導入數據前增加pga_aggregate_target大小，如果機器內存夠大，可從通常設定的500M提高到1-2G。pga_aggregate_target大小可以動態調整，導入完成後可在線調回原值。

2.4 調整BUFFER選項

Imp參數BUFFER定義了每一次讀取導出文件的數據量，設的越大，就越減少Import進程讀取數據的次數，從而提高導入效率。BUFFER的大小取決於系統應用、資料庫規模，通常來說，設為百兆就足夠了。其用法如下：

imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000

2.5 使用COMMIT=Y選項

COMMIT=Y表示每個數據緩沖滿了之後提交一次，而不是導完一張表提交一次。這樣會大大減少對系統回滾段等資源的消耗，對順利完成導入是有益的。

2.6 使用INDEXES=N選項

前面談到增加排序區時，說明Imp進程會先導入數據再創建索引。導入過程中建立用戶定義的索引，特別是表上有多個索引或者數據表特別龐大時，需要耗費大量時間。某些情況下，需要以最快的時間導入數據，而索引允許後建，我們就可以使用INDEXES=N 只導入數據不創建索引，從而加快導入速度。

我們可以用INDEXFILE選項生成創建索引的DLL腳本，再手工創建索引。我們也可以用如下的方法導入兩次，第一次導入數據，第二次導入索引。其用法如下：

imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_db_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=y indexes=n

imp user2/pwd fromuser=user1 touser=user2 file=/tmp/imp_index_pipe1 commit=y feedback=10000 buffer=10240000 ignore=y rows=n indexes=y

2.7 增加LARGE_POOL_SIZE

如果在init.ora中配置了MTS_SERVICE，MTS_DISPATCHERS等參數，tnsnames.ora中又沒有(SERVER=DEDICATED)的配置，那麼資料庫就使用了共享伺服器模式。在MTS模式下，Exp/Imp操作會用到LARGE_POOL，建議調整LARGE_POOL_SIZE到150M。

檢查資料庫是否在MTS模式下：

SQL>select distinct server from v$session;

如果返回值出現none或shared，說明啟用了MTS。

附錄：EXP/IMP命令選項注釋

>exp help=y

通過輸入 EXP 命令和用戶名/口令，您可以

後接用戶名/口令的命令:

常式: EXP SCOTT/TIGER

或者，您也可以通過輸入跟有各種參數的 EXP 命令來控制「導出」

按照不同參數。要指定參數，您可以使用關鍵字:

格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)

常式: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分區表

USERID 必須是命令行中的第一個參數。

關鍵字 說明(默認) 關鍵字 說明(默認)

--------------------------------------------------------------------------

USERID 用戶名/口令 FULL 導出整個文件 (N)

BUFFER 數據緩沖區大小 OWNER 所有者用戶名列表

FILE 輸出文件 (EXPDAT.DMP) TABLES 表名稱列表

COMPRESS 導入到一個區 (Y) RECORDLENGTH IO 記錄的長度

GRANTS 導出許可權 (Y) INCTYPE 增量導出類型

INDEXES 導出索引 (Y) RECORD 跟蹤增量導出 (Y)

DIRECT 直接路徑 (N) TRIGGERS 導出觸發器 (Y)

LOG 屏幕輸出的日誌文件 STATISTICS 分析對象 (ESTIMATE)

ROWS 導出數據行 (Y) PARFILE 參數文件名

CONSISTENT 交叉表的一致性 (N) CONSTRAINTS 導出的約束條件 (Y)

OBJECT_CONSISTENT 只在對象導出期間設置為讀的事務處理 (N)

FEEDBACK 每 x 行的顯示進度 (0)

FILESIZE 每個轉儲文件的最大大小

FLASHBACK_SCN 用於將會話快照設置回以前狀態的 SCN

FLASHBACK_TIME 用於獲取最接近指定時間的 SCN 的時間

QUERY 用於導出表的子集的 select 子句

RESUMABLE 遇到與空格相關的錯誤時掛起 (N)

RESUMABLE_NAME 用於標識可恢復語句的文本字元串

RESUMABLE_TIMEOUT RESUMABLE 的等待時間

TTS_FULL_CHECK 對 TTS 執行完整的或部分相關性檢查

TABLESPACES 要導出的表空間列表

TRANSPORT_TABLESPACE 導出可傳輸的表空間元數據 (N)

TEMPLATE 調用 iAS 模式導出的模板名

>imp help=y

可以通過輸入 IMP 命令和您的用戶名/口令

後接用戶名/口令的命令:

常式: IMP SCOTT/TIGER

或者, 可以通過輸入 IMP 命令和各種參數來控制「導入」

按照不同參數。要指定參數，您可以使用關鍵字:

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,...,vlaueN)

常式: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N

或 TABLES=(T1: P1,T1: P2)，如果 T1 是分區表

USERID 必須是命令行中的第一個參數。

關鍵字 說明（默認） 關鍵字 說明（默認）

------------------------------------------------------------------------

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)

FEEDBACK 每 x 行顯示進度 (0)

TOID_NOVALIDATE 跳過指定類型 ID 的驗證

FILESIZE 每個轉儲文件的最大大小

STATISTICS 始終導入預計算的統計信息

RESUMABLE 在遇到有關空間的錯誤時掛起 (N)

RESUMABLE_NAME 用來標識可恢復語句的文本字元串

RESUMABLE_TIMEOUT RESUMABLE 的等待時間

COMPILE 編譯過程, 程序包和函數 (Y)

STREAMS_CONFIGURATION 導入 Streams 的一般元數據 (Y)

STREAMS_INSTANITATION 導入 Streams 的實例化元數據 (N)

下列關鍵字僅用於可傳輸的表空間

TRANSPORT_TABLESPACE 導入可傳輸的表空間元數據 (N)

TABLESPACES 將要傳輸到資料庫的表空間

DATAFILES 將要傳輸到資料庫的數據文件

TTS_OWNERS 擁有可傳輸表空間集中數據的用戶

② oracle中怎樣導入資料庫文件

1、登錄到要導入的資料庫及用戶。

2、依次點擊「工具」——「導入表」。

3、輸入如下命令；

imp被導入用戶名/密碼@實例名file=文件名.dmplog=日誌.dmpfromuser=導出用戶touser=導入用戶

4、 輸入後按回車鍵，等待導入完成即可。

③ 如何使用oracle SQL developer導入表格數據

oracle是我們經常用到的資料庫，有時候我們需要往資料庫中導入大量的數據，這種數據如果通過sql插入是比較麻煩的，那麼如何用簡單的方法來把數據導入到資料庫中呢？

將文件整理成表格，第一行最好是用資料庫中對應的欄位名，後期會比較方便，存儲為.xls文件，使用.xlsx導入失敗。

打開oracle SQL developer，選中要導入的表名，右鍵選擇導入數據

選擇整理好的.xls文件

勾選標題，可以對數據進行預覽，同時方便下邊操作。預覽無誤後點擊下一步

選擇要插入資料庫中的欄位，也可點擊圖中標識位置選擇全部

選擇欄位後如下，點擊下一步

可以看到左側源數據列是你的表格第一行內容，如果在前邊不選標題此處為列0，列1等，自己無從下手。把源數據列和目標表列進行配對，軟體會自動給你按名字相同配對，切人無誤後點擊下一步

如果點擊發送到工作表會出現插入數據的sql語句，然後自己運行即可，如果不需要可以直接點擊完成。下面以點擊完成為例：

點擊完成後開始插入數據

插入完成後表中數據查詢如下，可看到數據導入成功。