Oracle數據庫備份與恢復之二：SQL*Loader

　　 基本知識

　　Oracle 的  SQL* LOADER  可以將外部格式化的文本數據加載到數據庫表中通常 與 SPOOL導出文本數據方法配合使用

　　命令格式

　　SQLLDR keyword=value [keyword=value……]

　　例$ sqlldr user/pwd control=empctl data=empdat bad=empbad log=emplog

　　控制文件

　　SQL*LOADER  根據控制文件可以找到需要加載的數據並且分析和解釋這些數據

　　控制文件由三個部分組成具體參數參考幫助文檔  全局選件行跳過的記錄數等 INFILE 子句指定的輸入數據  數據特性說明

　　comment ——注釋

　　例

　　load data infile *

　　append    ——除了 append外還有 insertreplacetruncate等方式

　　into table emp fields terminated b y |

　　（

　　no             float external name char（）

　　age           integer external

　　duty         char（）salary      float external

　　upd_ts     date（） YYYYMMDDHHMISS）

　　begindata

　　|Mulder||||

　　|Scully||||

　　控制文件中infile選項跟sqlldr 命令行中data 選項含義相同如使用infile *則表明數據在本控制文件以 begin data 開頭的區域內 一些選項FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x FILLER_ FILLER //  指定某一列將不會被裝載

　　DEPTNO position（） DNAME position（*） //  指定列的位置SEQNO RECNUM //載入每行的行號

　　SKIP n          //  指定導入時可以跳過多少行數據

　　數據文件

　　按控制文件數據格式定義的數據行集

　　例

　　|Tom||||

　　|Jerry||||

　　固定格式可變格式流記錄格式

　　固定格式

　　當數據固定的格式（長度一樣）時且是在文件中得到時要用 INFILE fix n

　　load data

　　infile exampledat fix

　　into table example

　　fields terminated b y optionally enclosed by

　　（col char（） col char（）） exampledat

　　 cd fghi

　　lmn

　　 pqrs

　　uvwx

　　可變格式

　　當數據是可變格式（長度不一樣）時且是在文件中得到時要用 INFILE var n如

　　load data

　　infile exampledat var

　　into table example

　　fields terminated b y optionally enclosed by

　　（col char（） col char（）） exampledat

　　hellocdworldim

　　myname is

　　流記錄格式 // Streamrecored formatload data infile xxdat str |\n

　　into table xx field terminated b y optionally enclosed by

　　（col char（） col char（））

　　exampledat

　　hello ccd|

　　world bb|

　　  壞文件

　　bad=empbad壞文件包含那些被 SQL*Loader拒絕的記錄被拒絕的記錄可能是不符合要求的記錄

　　  日志文件及日志信息

　　log=emplog當 SQL*Loader  開始執行後它就自動建立  日志文件日志文件包含有加載的總 結加載中的錯誤信息等

　　

　　  高級選項

　　 Conventional Path Load與Direct Path Load

　　Conventionalpath Load通過常規通道方式上載

　　特點commit always  gen redo logs  enforce all constraints fire insert triggers can load into cluster other user can make change

　　rows每次提交的記錄數

　　bindsize每次提交記錄的緩沖區

　　readsize與 bindsize 成對使用其中較小者會自動調整到較大者

　　sqlldr 先計算單條記錄長度乘以 rows如小於 bindsize不會試圖擴張rows以填充 bindsize如超出則以 bindsize 為准 命令為

　　$ sqlldr dbuser/oracle control=empctl log=emplog rows= bindsize=

　　DirectPath Load

　　通過直通方式上載可以跳過數據庫的相關邏輯不進行  SQL解析而直接將數 據導入到數據文件中

　　特點save conditionly gen redo logs enforce PK UK NN not fire triggers can not load into cluster other user can not make change命令為

　　$ sqlldr dbuser/oracle control=empctl log=emplog direct=true

　　 SPOOL導出文本數據方法

　　導入的數據文件可以用 SPOOL導出文本數據方法生成

　　SQL*PLUS環境設置

　　SET NEWPAGE NONE HEADING OFF SPACE

　　PAGESIZE SET TRIMOUT ON TRIMSPOOL ON LINESIZE

　　注LINESIZE 要稍微設置大些免得數據被截斷它應和相應的 TRIMSPOOL結合使用防止導出的文本有太多的尾部空格

　　但是如果 LINESIZE 設置太大會大大降低導出的速度另外在 WINDOWS下導 出最好不要用 PLSQL導出速度比較慢直接用  COMMEND 下的 SQLPLUS命令最 小化窗口執行對於字段內包含很多回車換行符的應該給與過濾形成比較規矩的文本 文件

　　通常情況下我們使用 SPOOL方法將數據庫中的表導出為文本文件如下述

　　set trimspool on

　　set linesize pagesize newpage heading off    term off spool  路徑+文件名

　　select col||||col||||col||||col||…… from tablename

　　spool off

　　

　　 腳本

　　  將表中數據記錄導出為字段值用分隔符|分開的dat文件

　　#！/bin/ksh

　　##################################################################

　　##    名稱 unloadtable

　　##    功能  本 shell 用於將表中數據記錄導出

　　##                 導出為字段值用分隔符|分開的dat文件

　　##    編者

　　##    日期

　　##################################################################

　　if [ $# ne ]

　　then echo usageunloadtable tablename username password

　　exit

　　fi

　　##准備工作

　　echo set heading off     >/tmp/$l

　　echo set pagesize >>/tmp/$l

　　echo set linesize     >>/tmp/$l

　　echo set feedback off    >>/tmp/$l

　　echo set tab off              >>/tmp/$l

　　echo  select  column_name||  from  user_tab_columns  where  lower（table_name）=$  order  by

　　column_id >> /tmp/$l

　　##產生 select 語句

　　echo set heading off     >/tmp/$sel

　　echo set pagesize >>/tmp/$sel

　　echo set linesize     >>/tmp/$sel

　　echo set feedback off    >>/tmp/$sel

　　echo set tab off              >>/tmp/$sel

　　echo select >>/tmp/$sel

　　echo  `sqlplus  s  $/$  <  /tmp/$l`  |sed  s//|||||/g  |sed  s/||$//g|sed  s/date/\date\/g

　　>>/tmp/$sel

　　##生成 dat文件

　　#echo from $\n/ >>/tmp/$sel    由於  /  導致多執行一次 select

　　echo from $\n >>/tmp/$sel

　　sqlplus s $/$ < /tmp/$sel >$_tmpdat

　　#awk {if（FNR！=） print $} $_tmpdat >$dat       FNR 選項使得第一條記錄選不出

　　awk {print $} $_tmpdat >$dat

　　rm f $_tmpdat

　　  將數據導入到相應表中

　　#！/bin/ksh

　　##################################################################

　　##    名稱loadtable

　　##    功能本 shell 用於將已經准備好的dat數據文件導入相應的表中

　　##               dat 文件各個字段值用分隔符|分開

　　##    編者

　　##    日期

　　##################################################################

　　if [ $# ne ]

　　then

　　echo usageloadtable tablename username    password exit fi

　　##准備工作

　　echo set heading off     >/tmp/$lsql

　　echo set pagesize >>/tmp/$lsql

　　echo set linesize     >>/tmp/$lsql

　　echo set feedback off    >>/tmp/$lsql

　　echo set tab off              >>/tmp/$lsql

　　echo  select  column_name||  from  user_tab_columns  where  lower（table_name）=$  order  by

　　column_id >> /tmp/$lsql

　　##產生 ctl文件

　　echo load data >/tmp/$ctl

　　echo infile * >>/tmp/$ctl

　　echo into table $ >>/tmp/$ctl

　　echo fields terminated by | >>/tmp/$ctl

　　echo `sqlplus s $/$ < /tmp/$lsql` |sed s/$/）/g |sed s/^/（/g >>/tmp/$ctl

　　##開始導入數據

　　echo truncate table $ >/tmp/$sql

　　sqlplus $/$ < /tmp/$sql

　　sqlldr $/$ data=$dat control=/tmp/$ctl log=/tmp/$log

From:http://tw.wingwit.com/Article/program/Oracle/201311/17312.html