《Oracle 常用匯入匯出工具集錦》

新年新群招募：中國Oracle精英聯盟 170513055
群介紹：本群是大家的一個技術分享社群，在這裡可以領略大師級的技術講座，還有機會參加Oracle舉辦的技術沙龍，與興趣相投的小夥伴一起笑談風雲起，感悟職場情!
一 資料庫版本

SYS@LEO1>select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

作業系統資訊

[oracle@leonarding1 admin]$ uname -a

Linux leonarding1.oracle.com 2.6.32-200.13.1.el5uek #1 SMP Wed Jul 27 21:02:33 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

二 比較使用sql*loader的直接載入方式和傳統載入方式的效能差異，給出演示過程和結論。

第一我們先要生成平面資料（文字資料）

LEO1@LEO1>create table leo2 as select * from dba_objects; 建立資料來源，我們的平面資料就是從這個表中取出

Table created.

第二我們利用spool工具將螢幕中顯示出來的記錄寫入到指定檔案，這樣我們就可以得到一個平面檔案啦

set termout off; 是否在螢幕上顯示輸出內容，off螢幕不顯示查詢語句，主要與spool結合使用

set feedback off; 關閉本次sql命令處理的記錄條數，預設為on即去掉最後的已經選擇的行數

set echo off; 關閉指令碼中正在執行的SQL語句的顯示

set heading off; 關閉標題的輸出，設定為off就去掉了select結果的欄位名只顯示資料

set trimout on; 去除標準輸出每行後面多餘的空格

set trimspool on; 將每行後面多餘的空格去掉【linesize-實際字元數=多餘空格】

spool /home/oracle/sql_loader/leo3.txt 在螢幕上的所有內容都包含在該檔案中

select owner||','||object_name||','||object_id||','||object_type from leo2;

spool off 只有關閉spool輸出，才會在輸出檔案中看到輸出的內容

備註：在實用SPOOL輸出內容到本地檔案時，需注意編碼格式，否則會出現亂碼的問題

[oracle@leonarding1 sql_loader]$ ll

total 28468

-rw-r--r-- 1 oracle oinstall 3246601 Jun 22 14:06 leo3.txt 已經生成平面檔案leo3.txt

[oracle@leonarding1 sql_loader]$ cat leo3.txt | wc -l 檔案中有72678行記錄

72678

第三建立裝入的表leo3_loader

LEO1@LEO1>create table leo3_loader

(

owner varchar2(30),

object_name varchar2(130),

object_id number,

object_type varchar2(20)

);

2 3 4 5 6 7

第四建立sql*loader的控制檔案leo3_loader.ctl

[oracle@leonarding1 sql_loader]$ vim leo3_loader.ctl

load data

infile '/home/oracle/sql_loader/leo3.txt' 待載入的資料檔案

badfile '/home/oracle/sql_loader/leo3_bad.txt' 格式不匹配寫入壞檔案

discardfile '/home/oracle/sql_loader/leo3_discard.txt' 條件不匹配寫入丟棄檔案

append into table leo3_loader 追加的方式插入資料

fields terminated by "," 欄位與欄位之間的分隔符

trailing nullcols 這句的意思是將沒有對應值的列都置為null

(owner,object_name,object_id,object_type) 資料插入的對應欄位

第五執行sqlldr直接載入命令

[oracle@leonarding1 sql_loader]$ sqlldr leo1/leo1 control=leo3_loader.ctl log=leo3_loader.log direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Sat Jun 22 14:08:31 2013

Load completed - logical record count 72678.

已經載入了72678行，條件不匹配有72行，實際載入入72606行

LEO1@LEO1>select count(*) from leo3_loader; 表中也是

72606

我們在看一下sql*loader日誌

。。。省略前部份。。。

Total logical records skipped: 0

Total logical records read: 72678

Total logical records rejected: 0

Total logical records discarded: 72 條件不匹配有72行

Total stream buffers loaded by SQL*Loader main thread: 17

Total stream buffers loaded by SQL*Loader load thread: 6

Run began on Sat Jun 22 14:08:31 2013

Run ended on Sat Jun 22 14:08:34 2013

Elapsed time was: 00:00:02.60 所用耗時2.6秒

CPU time was: 00:00:00.13

使用conventional傳統載入方式寫入資料

LEO1@LEO1>truncate table leo3_loader; 清空表在載入一次

[oracle@leonarding1 sql_loader]$ sqlldr leo1/leo1 control=leo3_loader.ctl log=leo3_loader.log

LEO1@LEO1>select count(*) from leo3_loader; 表中也是

72606

我們在看一下sql*loader日誌

。。。省略前部份。。。

Total logical records skipped: 0

Total logical records read: 72678

Total logical records rejected: 0

Total logical records discarded: 72 條件不匹配有72行

Run began on Sat Jun 22 15:25:45 2013

Run ended on Sat Jun 22 15:26:05 2013

Elapsed time was: 00:00:20.79 所用耗時2.6秒

CPU time was: 00:00:00.48

小結：經過比對direct比conventional要提高了20倍效率，為什麼direct會這麼高效呢，下面我們來說說這兩種的區別。

Direct 特點

（1）資料繞過SGA直接寫入磁碟的資料檔案

（2）資料直接寫入高水位線HWM之後的新塊，不會掃描HWM之前的空閒塊

（3）commit之後移動HWM他人才能看到

（4）不對已用空間進行掃描

（5）使用direct幾乎不產生redo log，不是完全不產生(安全性差)，但會產生undo資料

（6）適用OLAP線上分析場景，增刪改不頻繁的場景

Conventional傳統載入特點

（1）資料先載入 -> SGA -> 磁碟的資料檔案

（2）會掃描高水位線HWM之前的資料塊，如果有空閒塊(碎片經常DML導致)就會利用，如果沒有再插入新塊

（3）高水位線HWM之前的資料塊是放在SGA區的

（4）會產生redo log和undo資料

（5）安全性高，可恢復資料

（6）傳統載入與SQL語句insert插入沒區別

三 比較資料泵和exp/imp對相同資料匯出/匯入的效能差異，給出演示過程

EXP/IMP 是資料庫傳統的匯出匯入工具，它有使用方便，在服務端和客戶端都可用的優點，速度沒有expdp快，功能沒有expdp強大

EXP 工具幫助文件，我們可以參考幫助文件進行命令列配置

[oracle@leonarding1 ~]$ exp –help 或者 exp help=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 20 07:28:33 2013

You can let Export prompt you for parameters by entering the EXP

command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed

by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

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

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)

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

USERID username/password FULL export entire file (N)

BUFFER size of data buffer OWNER list of owner usernames

FILE output files (EXPDAT.DMP) TABLES list of table names

COMPRESS import into one extent (Y) RECORDLENGTH length of IO record

GRANTS export grants (Y) INCTYPE incremental export type

INDEXES export indexes (Y) RECORD track incr. export (Y)

DIRECT direct path (N) TRIGGERS export triggers (Y)

LOG log file of screen output STATISTICS analyze objects (ESTIMATE)

ROWS export data rows (Y) PARFILE parameter filename

CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)

FEEDBACK display progress every x rows (0)

FILESIZE maximum size of each dump file

FLASHBACK_SCN SCN used to set session snapshot back to

FLASHBACK_TIME time used to get the SCN closest to the specified time

QUERY select clause used to export a subset of a table

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

TTS_FULL_CHECK perform full or partial dependency check for TTS

VOLSIZE number of bytes to write to each tape volume

TABLESPACES list of tablespaces to export

TRANSPORT_TABLESPACE export transportable tablespace metadata (N)

TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

建立測試表leo1

LEO1@LEO1>set linesize 400 pagesize 999 格式化

LEO1@LEO1>drop table leo1 purge; 清空環境

Table dropped.

LEO1@LEO1>create table leo1 as select * from dba_objects; 建立測試表leo1

Table created.