oraclesqlloader全攻略

一sql loader 的特點
oracle自己帶了很多的工具可以用來進行數據的遷移備份和恢復等工作但是每個工具都有自己的特點
比如說exp和imp可以對數據庫中的數據進行導出和導出的工作是一種很好的數據庫備份和恢復的工具因此主要用在數據庫的熱備份和恢復方面有著速度快使用簡單快捷的優點同時也有一些缺點比如在不同版本數據庫之間的導出導入的過程之中總會出現這樣或者那樣的問題這個也許是oracle公司自己產品的兼容性的問題吧
sql loader 工具卻沒有這方面的問題它可以把一些以文本格式存放的數據順利的導入到oracle數據庫中是一種在不同數據庫之間進行數據遷移的非常方便而且通用的工具缺點就速度比較慢另外對blob等類型的數據就有點麻煩了

二sql loader 的幫助

　　C:\>sqlldr

　　SQL*Loader: Release Production on 星期六 月 ::

　　Copyright (c) Oracle Corporation All rights reserved

　　用法: SQLLDR keyword=value [keyword=value]

　　有效的關鍵字:

　　userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow (全部默認)
skip Number of logical records to skip (默認)
load Number of logical records to load (全部默認)
errors Number of errors to allow (默認)
rows Number of rows in conventional path bind array or between direct p
ath data saves
（默認: 常規路徑 所有直接路徑）
bindsize Size of conventional path bind array in bytes(默認)
silent Suppress messages during run (headerfeedbackerrorsdiscardspart
itions)
direct use direct path (默認FALSE)
parfile parameter file: name of file that contains parameter specification
s
parallel do parallel load (默認FALSE)
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions(默
認FALSE)
skip_index_maintenance do not maintain indexes mark affected indexes as unus
able(默認FALSE)
readsize Size of Read buffer (默認)
external_table use external table for load; NOT_USED GENERATE_ONLY EXECUTE(
默認NOT_USED)
columnarrayrows Number of rows for direct path column array(默認)
streamsize Size of direct path stream buffer in bytes(默認)
multithreading use multithreading in direct path
resumable enable or disable resumable for current session(默認FALSE)
resumable_name text string to help identify resumable statement
resumable_timeout wait time (in seconds) for RESUMABLE(默認)
date_cache size (in entries) of date conversion cache(默認)

　　PLEASE NOTE: 命令行參數可以由位置或關鍵字指定
前者的例子是 sqlload
scott/tiger foo; 後一種情況的一個示例是 sqlldr control=foo
userid=scott/tiger位置指定參數的時間必須早於
但不可遲於由關鍵字指定的參數例如
允許 sqlldr scott/tiger control=foo logfile=log 但是
不允許 sqlldr scott/tiger control=foo log 即使
參數 log 的位置正確

　　C:\>

　　三sql loader使用例子
a）SQLLoader將 Excel 數據導出到 Oracle
創建SQL*Loader輸入數據所需要的文件均保存到C:\用記事本編輯
控制文件inputctl內容如下

　　 load data　　　　　　　　　　 控制文件標識
　　 infile testtxt　　　　　　 要輸入的數據文件名為testtxt
　　 append into table test　　　　向表test中追加記錄
　　 fields terminated by X　　字段終止於X是一個制表符（TAB）
　　 (idusernamepasswordsj)　　 定義列對應順序

ainsert為缺省方式在數據裝載開始時要求表為空
bappend在表中追加新記錄
creplace刪除舊記錄替換成新裝載的記錄
dtruncate同上

在DOS窗口下使用SQL*Loader命令實現數據的輸入

C:\>sqlldr userid=system/manager control=inputctl
默認日志文件名為inputlog
默認壞記錄文件為inputbad

還有一種方法
可以把EXCEL文件另存為CSV（逗號分隔）(*csv)控制文件就改為用逗號分隔
LOAD DATA
INFILE d:\carcsv
APPEND INTO TABLE t_car_temp
FIELDS TERMINATED BY
(phonenovip_car)

　　b）在控制文件中直接導入數據

　　控制文件testctl的內容
The format for executing this file with SQL Loader is:
SQLLDR control=<filename> Be sure to substitute your
version of SQL LOADER and the filename for this file
LOAD DATA
INFILE *
BADFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYBAD
DISCARDFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYDSC
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ; Optionally enclosed by
(
COUNTRYID NULLIF (COUNTRYID=NULL)
COUNTRYCODE
COUNTRYNAME
CONTINENTID NULLIF (CONTINENTID=NULL)
MAPID NULLIF (MAPID=NULL)
CREATETIME DATE MM/DD/YYYY HH:MI:SS NULLIF (CREATETIME=NULL)
LASTMODIFIEDTIME DATE MM/DD/YYYY HH:MI:SS NULLIF (LASTMODIFIEDTIME=NULL)
)
BEGINDATA
;JP;Japan;;;// ::;NULL
;CN;China;;;// ::;NULL
;IN;India;;;// ::;NULL
;AU;Australia;;;// ::;NULL
;CA;Canada;;;// ::;NULL
;US;United States;;;// ::;NULL
;MX;Mexico;;;// ::;NULL
;GB;United Kingdom;;;// ::;NULL
;DE;Germany;;;// ::;NULL
;FR;France;;;// ::;NULL
;IT;Italy;;;// ::;NULL
;ES;Spain;;;// ::;NULL
;FI;Finland;;;// ::;NULL
;SE;Sweden;;;// ::;NULL
;IE;Ireland;;;// ::;NULL
;NL;Netherlands;;;// ::;NULL
;DK;Denmark;;;// ::;NULL
;BR;Brazil;;;// ::;NULL
;KR;Korea Republic of;;;// ::;NULL
;NZ;New Zealand;;;// ::;NULL
;BE;Belgium;;;// ::;NULL
;AT;Austria;;;// ::;NULL
;NO;Norway;;;// ::;NULL
;LU;Luxembourg;;;// ::;NULL
;PT;Portugal;;;// ::;NULL
;GR;Greece;;;// ::;NULL
;IL;Israel;;;// ::;NULL
;CH;Switzerland;;;// ::;NULL
;A;Anonymous Proxy;;;// ::;NULL
;A;Satellite Provider;;;// ::;NULL
;AD;Andorra;;;// ::;NULL
;AE;United Arab Emirates;;;// ::;NULL
;AF;Afghanistan;;;// ::;NULL
;AG;Antigua and Barbuda;;;// ::;NULL
;AI;Anguilla;;;// ::;NULL
;AL;Albania;;;// ::;NULL
;AM;armenia;;;// ::;NULL
;AN;Netherlands Antilles;;;// ::;NULL
;AO;Angola;;;// ::;NULL
;AP;Asia/Pacific Region;;;// ::;NULL
;AQ;Antarctica;;;// ::;NULL
;AR;Argentina;;;// ::;NULL
;AS;American Samoa;;;// ::;NULL
;AW;Aruba;;;// ::;NULL
;AZ;Azerbaijan;;;// ::;NULL
;BA;Bosnia and Herzegovina;;;// ::;NULL
;BB;Barbados;;;// ::;NULL
;BD;Bangladesh;;;// ::;NULL
;BF;Burkina Faso;;;// ::;NULL
;BG;Bulgaria;;;// ::;NULL
;BH;Bahrain;;;// ::;NULL
;BI;Burundi;;;// ::;NULL
;BJ;Benin;;;// ::;NULL
;BM;Bermuda;;;// ::;NULL
;BN;Brunei Darussalam;;;// ::;NULL
;BO;Bolivia;;;// ::;NULL
;BS;Bahamas;;;// ::;NULL
;BT;Bhutan;;;// ::;NULL
;BV;Bouvet Island;;;// ::;NULL
;BW;Botswana;;;// ::;NULL
;BY;Belarus;;;// ::;NULL
執行導入命令
C:\>sqlldr userid=system/manager control=testctl

　　 

　　 

　　 

　　 

　　part ii

SQL*Loader是Oracle數據庫導入外部數據的一個工具它和DB的Load工具相似但有更多的選擇它支持變化的加載模式可選的加載及多表加載
　　如何使用 SQL*Loader 工具
　　我們可以用Oracle的sqlldr工具來導入數據例如:
　　sqlldr scott/tiger control=loaderctl
　　控制文件(loaderctl) 將加載一個外部數據文件（含分隔符) loaderctl如下:
　　load data
　　infile c:\data\mydatacsv
　　into table emp
　　fields terminated by optionally enclosed by
　　( empno empname sal deptno )
　　mydatacsv 如下:
　　Scott Tiger
　　Frank Naude
　　下面是一個指定記錄長度的示例控制文件* 代表數據文件與此文件同名即在後面使用BEGINDATA段來標識數據
　　load data
　　infile *
　　replace
　　into table departments
　　( dept position (:) char()
　　deptname position (:) char()
　　)
　　begindata
　　COSC COMPUTER SCIENCE
　　ENGL ENGLISH LITERATURE
　　MATH MATHEMATICS
　　POLY POLITICAL SCIENCE
　　Unloader這樣的工具
　　Oracle 沒有提供將數據導出到一個文件的工具但是我們可以用SQL*Plus的select 及 format 數據來輸出到一個文件
　　set echo off newpage space pagesize feed off head off trimspool on
　　spool oradatatxt
　　select col || || col || || col
　　from tab
　　where col = XYZ;
　　spool off
　　另外也可以使用使用 UTL_FILE PL/SQL 包處理:
　　rem Remember to update initSIDora utl_file_dir=c:\oradata parameter
　　declare
　　fp utl_filefile_type;
　　begin
　　fp := utl_filefopen(c:\oradatatabtxtw);
　　utl_fileputf(fp %s %s\n TextField );
　　utl_filefclose(fp);
　　end;
　　/
　　當然你也可以使用第三方工具如SQLWays TOAD for Quest等
　　加載可變長度或指定長度的記錄
　　如
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_delimited_data
　　FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
　　TRAILING NULLCOLS
　　( data
　　data
　　)
　　BEGINDATA
　　AAAAAAAAAA
　　ABCD
　　下面是導入固定位置（固定長度）數據示例
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　( data POSITION(:)
　　data POSITION(:)
　　)
　　BEGINDATA
　　AAAAAAAAAA
　　BBBBBBBBBB
　　跳過數據行
　　可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據如
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　SKIP
　　( data POSITION(:)
　　data POSITION(:)
　　)
　　BEGINDATA
　　AAAAAAAAAA
　　BBBBBBBBBB
　　導入數據時修改數據
　　在導入數據到數據庫時可以修改數據注意這僅適合於常規導入並不適合 direct導入方式如
　　LOAD DATA
　　INFILE *
　　INTO TABLE modified_data
　　( rec_no my_db_sequencenextval
　　region CONSTANT
　　time_loaded to_char(SYSDATE HH:MI)
　　data POSITION(:) :data/
　　data POSITION(:) upper(:data)
　　data POSITION(:)to_date(:data YYMMDD)
　　)
　　BEGINDATA
　　AAAAAAAAAA
　　BBBBBBBBBB
　　LOAD DATA
　　INFILE mail_orderstxt
　　BADFILE bad_orderstxt
　　APPEND
　　INTO TABLE mailing_list
　　FIELDS TERMINATED BY
　　( addr
　　city
　　state
　　zipcode
　　mailing_addr decode(:mailing_addr null :addr :mailing_addr)
　　mailing_city decode(:mailing_city null :city :mailing_city)
　　mailing_state
　　)
　　將數據導入多個表
　　如:
　　LOAD DATA
　　INFILE *
　　REPLACE
　　INTO TABLE emp
　　WHEN empno !=
　　( empno POSITION(:) INTEGER EXTERNAL
　　ename POSITION(:) CHAR
　　deptno POSITION(:) CHAR
　　mgr POSITION(:) INTEGER EXTERNAL
　　)
　　INTO TABLE proj
　　WHEN projno !=
　　( projno POSITION(:) INTEGER EXTERNAL
　　empno POSITION(:) INTEGER EXTERNAL
　　)
　　導入選定的記錄
　　如下例 () 代表第一個字符 (:) 代表到之間的字符:
　　LOAD DATA
　　INFILE mydatadat BADFILE mydatabad DISCARDFILE mydatadis
　　APPEND
　　INTO TABLE my_selective_table
　　WHEN () <> H and () <> T and (:) =
　　(
　　region CONSTANT
　　service_key POSITION(:) INTEGER EXTERNAL
　　call_b_no POSITION(:) CHAR
　　)
　　導入時跳過某些字段
　　可用 POSTION(x:y) 來分隔數據 在Oraclei中可以通過指定 FILLER 字段實現FILLER 字段用來跳過忽略導入數據文件中的字段如
　　LOAD DATA
　　TRUNCATE INTO TABLE T
　　FIELDS TERMINATED BY
　　( field
　　field FILLER
　　field
　　)
　　導入多行記錄
　　可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:
　　CONCATENATE: use when SQL*Loader should combine the same number of physical records together to form one logical record
　　CONTINUEIF use if a condition indicates that multiple records should be treated as one Eg by having a # character in column
　　SQL*Loader 數據的提交
　　一般情況下是在導入數據文件數據後提交的
　　也可以通過指定 ROWS= 參數來指定每次提交記錄數
　　提高 SQL*Loader 的性能
　　) 一個簡單而容易忽略的問題是沒有對導入的表使用任何索引和/或約束(主鍵)如果這樣做甚至在使用ROWS=參數時會很明顯降低數據庫導入性能
　　) 可以添加 DIRECT=TRUE來提高導入數據的性能當然在很多情況下不能使用此參數
　　) 通過指定 UNRECOVERABLE選項可以關閉數據庫的日志這個選項只能和 direct 一起使用
　　) 可以同時運行多個導入任務
　　常規導入與direct導入方式的區別
　　常規導入可以通過使用 INSERT語句來導入數據Direct導入可以跳過數據庫的相關邏輯(DIRECT=TRUE)而直接將數據導入到數據文件中

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