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

2.1 基本知識

Oracle 的  SQL* LOADER  可以將外部格式化的文本數據加載到數據庫表中。通常 與 SPOOL導出文本數據方法配合使用。   1．命令格式   SQLLDR keyword=value [,keyWord=value,...] 例： $ sqlldr user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log   2．控制文件   SQL*LOADER  根據控制文件可以找到需要加載的數據。並且分析和解釋這些數 據。 控制文件由三個部分組成,具體參數參考幫助文檔： 1.  全局選件，行，跳過的記錄數等； 2. INFILE 子句指定的輸入數據； 3.  數據特性說明。 comment: --注釋 例： load data infile * append    --除了 append外，還有 insert、replace、truncate等方式 into table emp fIElds terminated b y ‘|’ ( no             float external, name char(20), age           integer external, duty         char(1), salary      float external, upd_ts     date(14) ‘YYYYMMDDHH24MISS’ ) begindata 100000000003|Mulder|000020|1|000000005000|20020101000000 100000000004|Scully|000025|2|000000008000|20020101235959   控制文件中 infile選項跟 sqlldr 命令行中 data 選項含義相同，如使用 infile *則表明 數據在本控制文件以 begin data 開頭的區域內。 一些選項： FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' FILLER_1 FILLER, //  指定某一列將不會被裝載 DEPTNO position(1:2), DNAME position(*:16), //  指定列的位置 SEQNO RECNUM //載入每行的行號 SKIP n          //  指定導入時可以跳過多少行數據   3．數據文件   按控制文件數據格式定義的數據行集，例： 100000000001|Tom|000020|1|000000005000|20020101000000 100000000002|Jerry|000025|2|000000008000|20020101235959     固定格式、可變格式、流記錄格式： 固定格式： 當數據固定的格式（長度一樣）時且是在文件中得到時，要用 INFILE "fix n" load data infile 'example.dat' "fix 11" into table example fIElds terminated b y ',' optionally enclosed by '"' (col1 char(5), col2 char(7)) example.dat: 001, cd, 0002,fghi, 00003,lmn, 1, "pqrs", 0005,uvwx, 可變格式: 當數據是可變格式（長度不一樣）時且是在文件中得到時，要用 INFILE "var n"。如： load data infile 'example.dat' "var 3" into table example fIElds terminated b y ',' optionally enclosed by '"' (col1 char(5), col2 char(7)) example.dat: 009hello,cd,010world,im, 012my,name is, 流記錄格式: // Stream-recored format: load data infile 'xx.dat' "str '|\n'"   into table xx fIEld terminated b y ',' optionally enclosed by '"' (col1 char(5), col2 char(7))     example.dat: hello, ccd,| world, bb,|   4.  壞文件     bad=emp.bad 壞文件包含那些被 SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記 錄。   5.  日志文件及日志信息   log=emp.log 當 SQL*Loader  開始執行後，它就自動建立  日志文件。日志文件包含有加載的總 結，加載中的錯誤信息等。

2.2  高級選項

1. Conventional Path Load  與  Direct Path Load   Conventional-path 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=emp.ctl log=emp.log rows=10000 bindsize=8192000 Direct-Path 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=emp.ctl log=emp.log direct=true   2. SPOOL導出文本數據方法   導入的數據文件可以用 SPOOL導出文本數據方法生成。 SQL*PLUS環境設置 SET NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0 SET TRIMOUT ON TRIMSPOOL ON LINESIZE 2500 注：LINESIZE 要稍微設置大些，免得數據被截斷，它應和相應的 TRIMSPOOL結合使用防止導出的文本有太多的尾部空格。 但是如果 LINESIZE 設置太大，會大大降低導出的速度，另外在 Windows下導 出最好不要用 PLSQL導出，速度比較慢，直接用  COMMEND 下的 SQLPLUS命令最 小化窗口執行。對於字段內包含很多回車換行符的應該給與過濾，形成比較規矩的文本 文件。 通常情況下，我們使用 SPOOL方法，將數據庫中的表導出為文本文件，如下述： set trimspool on set linesize 120 pagesize 2000 newpage 1 heading off    term off spool  路徑+文件名 select col1||','||col2||','||col3||','||col4||'..' from tablename; spool off

2.3 腳本

 1.  將表中數據記錄導出為字段值用分隔符'|'分開的.dat文件   #!/bin/ksh ################################################################## ##    名稱: unloadtable ##    功能:  本 shell 用於將表中數據記錄導出 ##                 導出為字段值用分隔符'|'分開的.dat文件 ##    編者： ##    日期: 2006.03.18 ##################################################################   if [ $# -ne 3 ] then echo "usage:unloadtable tablename username passWord." exit 0 fi   ##准備工作 echo "set heading off     " >/tmp/$1.col echo "set pagesize 0" >>/tmp/$1.col echo "set linesize 800    " >>/tmp/$1.col echo "set feedback off    " >>/tmp/$1.col   echo "set tab off              " >>/tmp/$1.col echo  "select  column_name||','  from  user_tab_columns  where  lower(table_name)='$1'  order  by column_id; " >> /tmp/$1.col   ##產生 select 語句 echo "set heading off     " >/tmp/$1.sel echo "set pagesize 0" >>/tmp/$1.sel echo "set linesize 800    " >>/tmp/$1.sel echo "set feedback off    " >>/tmp/$1.sel echo "set tab off              " >>/tmp/$1.sel echo "select " >>/tmp/$1.sel echo  `sqlplus  -s  $2/$3  <  /tmp/$1.col`  |sed  "s/,/||'|'||/g"  |sed  "s/||$//g"|sed  "s/date/\"date\"/g" >>/tmp/$1.sel   ##生成 dat文件 #echo "from $1;\n/" >>/tmp/$1.sel    由於  /  導致多執行一次 select echo "from $1;\n" >>/tmp/$1.sel sqlplus -s $2/$3 < /tmp/$1.sel >$1_tmp.dat #awk '{if(FNR!=1) print $0}' $1_tmp.dat >$1.dat       FNR 選項使得第一條記錄選不出 awk '{print $0}' $1_tmp.dat >$1.dat rm -f $1_tmp.dat   2.  將數據導入到相應表中   #!/bin/ksh ################################################################## ##    名稱:loadtable ##    功能:本 shell 用於將已經准備好的.dat數據文件導入相應的表中 ##               .dat 文件各個字段值用分隔符'|'分開。 ##    編者： ##    日期: 2006.03.18 ################################################################## if [ $# -ne 3 ] then echo "usage:loadtable tablename username    passWord." exit 0 fi   ##准備工作 echo "set heading off     " >/tmp/$1.colsql   echo "set pagesize 0" >>/tmp/$1.colsql echo "set linesize 800    " >>/tmp/$1.colsql echo "set feedback off    " >>/tmp/$1.colsql echo "set tab off              " >>/tmp/$1.colsql echo  "select  column_name||','  from  user_tab_columns  where  lower(table_name)='$1'  order  by column_id; " >> /tmp/$1.colsql   ##產生 ctl文件 echo "load data" >/tmp/$1.ctl echo "infile *" >>/tmp/$1.ctl echo "into table $1" >>/tmp/$1.ctl echo "fIElds terminated by '|'" >>/tmp/$1.ctl echo `sqlplus -s $2/$3 < /tmp/$1.colsql` |sed "s/,$/)/g" |sed "s/^/(/g" >>/tmp/$1.ctl   ##開始導入數據 echo "truncate table $1;" >/tmp/$1.sql sqlplus $2/$3 < /tmp/$1.sql sqlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log