使用oracle sqlldr匯入文字資料的例子
一：sql loader使用例子
car.csv要匯入的資料格式如下
姓名 性 別 身份證號碼 人員類別 未登帳時間
張宏華 男 432401511011201 有工作（區） 2011.04-2012.03
胡祥勝 男 有工作（41市 2011.04-2012.03
楊長保 男 430702195111221011 有工作（區） 2011.04-2012.03
劉新翁 男 432401511125001 有工作（41市2011.04-2012.03
周家勝 男 432401511123001 有工作（(市）2011.04-2012.03
顧春茂 男 432401510210301 有工作（市） 2011.04-2012.03
王躍志 男 432401195102200015 有工作（41區2011.04-2012.03
潘伯倫 男 432401195112237516 老市“4050” 2011.04-2012.03
a.建立表(將上面的資料匯入到這個表中)
create table jy_20120308
(
username varchar2(40),
sex varchar2(10),
idcard varchar2(20),
rylb varchar2(50),
sj varchar2(50)
)
a.建立SQL*Loader輸入資料所需要的檔案,均儲存到C:\，用記事本編輯：
控制檔案：input.ctl，內容如下：
load data --1、控制檔案標識
infile 'D:\sqlldr\tx.csv' --2、要輸入的資料檔名為car.csv
append into table jy_20120308 --3、向表test中追加記錄
fields terminated by "," --4、欄位以逗號分隔
(username,sex,idcard,rylb,sj) -----定義列對應順序
a、insert，為預設方式，在資料裝載開始時要求表為空
b、append，在表中追加新記錄
c、replace，刪除舊記錄，替換成新裝載的記錄
d、truncate，同上
在DOS視窗下使用SQL*Loader命令實現資料的輸入
C:\>sqlldr userid=system/manager@orcl control=input.ctl
預設日誌檔名為：input.log
預設壞記錄檔案為：input.bad
也可以使用下面形式
C:\>sqlldr userid=system/manager@orcl control=input.ctl
data=/export/home/port.txt log=port.log
建立SQL*Loader輸入資料所需要的檔案,均儲存到C:\，用記事本編輯：
控制檔案：input.ctl，內容如下：
load data --1、控制檔案標識
infile * --2、可以在控制檔案內或在執行命令時指定 --資料內容或資料檔名
append into table jy_20120308 --3、向表test中追加記錄
fields terminated by "," --4、欄位以逗號分隔
(username,sex,idcard,rylb,sj) -----定義列對應順序
sqlldr 'DBUser/Password control=/export/home/imp.ctl'
data=
D:\sqlldr\tx.csv' log=port.log
data=/export/home/port.txt log=port.log
b）在控制檔案中直接匯入資料
1、控制檔案test.ctl的內容
LOAD DATA
INFILE *
BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'
DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'
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 HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL
3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL
4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL
5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL
6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL
7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL
8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL
9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL
10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL
11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL
12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL
13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL
14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL
15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL
16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL
17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL
18;"BR";"Brazil";5;85;"09/30/2004 11:25:43";NULL
19;"KR";"Korea, Republic of";1;88;"09/30/2004 11:25:43";NULL
20;"NZ";"New Zealand";6;89;"09/30/2004 11:25:43";NULL
21;"BE";"Belgium";3;79;"09/30/2004 11:25:43";NULL
22;"AT";"Austria";3;78;"09/30/2004 11:25:43";NULL
23;"NO";"Norway";3;82;"09/30/2004 11:25:43";NULL
24;"LU";"Luxembourg";3;81;"09/30/2004 11:25:43";NULL
25;"PT";"Portugal";3;83;"09/30/2004 11:25:43";NULL
26;"GR";"Greece";3;80;"09/30/2004 11:25:43";NULL
27;"IL";"Israel";1;86;"09/30/2004 11:25:43";NULL
28;"CH";"Switzerland";3;84;"09/30/2004 11:25:43";NULL
29;"A1";"Anonymous Proxy";0;0;"09/30/2004 11:25:43";NULL
30;"A2";"Satellite Provider";0;0;"09/30/2004 11:25:43";NULL
31;"AD";"Andorra";3;0;"09/30/2004 11:25:43";NULL
32;"AE";"United Arab Emirates";1;0;"09/30/2004 11:25:43";NULL
33;"AF";"Afghanistan";1;0;"09/30/2004 11:25:43";NULL
34;"AG";"Antigua and Barbuda";7;0;"09/30/2004 11:25:43";NULL
35;"AI";"Anguilla";7;0;"09/30/2004 11:25:43";NULL
36;"AL";"Albania";3;0;"09/30/2004 11:25:43";NULL
37;"AM";"Armenia";3;0;"09/30/2004 11:25:43";NULL
38;"AN";"Netherlands Antilles";3;0;"09/30/2004 11:25:43";NULL
39;"AO";"Angola";2;0;"09/30/2004 11:25:43";NULL
40;"AP";"Asia/Pacific Region";2;0;"09/30/2004 11:25:43";NULL
41;"AQ";"Antarctica";8;0;"09/30/2004 11:25:43";NULL
42;"AR";"Argentina";5;0;"09/30/2004 11:25:43";NULL
43;"AS";"American Samoa";6;0;"09/30/2004 11:25:43";NULL
44;"AW";"Aruba";5;0;"09/30/2004 11:25:43";NULL
45;"AZ";"Azerbaijan";1;0;"09/30/2004 11:25:43";NULL
46;"BA";"Bosnia and Herzegovina";3;0;"09/30/2004 11:25:43";NULL
47;"BB";"Barbados";5;0;"09/30/2004 11:25:43";NULL
48;"BD";"Bangladesh";1;0;"09/30/2004 11:25:43";NULL
49;"BF";"Burkina Faso";2;0;"09/30/2004 11:25:43";NULL
50;"BG";"Bulgaria";3;0;"09/30/2004 11:25:43";NULL
51;"BH";"Bahrain";1;0;"09/30/2004 11:25:43";NULL
52;"BI";"Burundi";2;0;"09/30/2004 11:25:43";NULL
53;"BJ";"Benin";2;0;"09/30/2004 11:25:43";NULL
54;"BM";"Bermuda";4;0;"09/30/2004 11:25:43";NULL
55;"BN";"Brunei Darussalam";1;0;"09/30/2004 11:25:43";NULL
56;"BO";"Bolivia";5;0;"09/30/2004 11:25:43";NULL
57;"BS";"Bahamas";7;0;"09/30/2004 11:25:43";NULL
58;"BT";"Bhutan";1;0;"09/30/2004 11:25:43";NULL
59;"BV";"Bouvet Island";5;0;"09/30/2004 11:25:43";NULL
60;"BW";"Botswana";2;0;"09/30/2004 11:25:43";NULL
61;"BY";"Belarus";3;0;"09/30/2004 11:25:43";NULL
2、執行匯入命令
C:\>sqlldr userid=system/manager control=test.ctl
c）複雜格式的匯入
Sqlldr
sql loader可以把一些以文字格式存放的資料順利的匯入到oracle資料庫中，
是一種在不同資料庫之間進行資料遷移的非常方便而且通用的工具。
缺點就速度比較慢，另外對blob等型別的資料就有點麻煩了。
用法: SQLLDR keyword=value [,keyword=value,...]
有效的關鍵字:
userid -- ORACLE username/password
control - 控制檔案
log - 記錄的日誌檔案
bad - 壞資料檔案
data - 資料檔案
discard - 丟棄的資料檔案
discardmax - 允許丟棄資料的最大值 (全部預設)
skip -- Number of logical records to skip (預設0)
load -- Number of logical records to load (全部預設)
errors - 允許的錯誤記錄數 (預設50)
rows -- Number of rows in conventional path bind array or between direct path data saves
（每次提交的記錄數，預設: 常規路徑 64, 所有直接路徑）
bindsize -- Size of conventional path bind array in bytes(預設256000)
每次提交記錄的緩衝區的大小(位元組為單位，預設256000)
silent --禁止輸出資訊 (header,feedback,errors,discards,partitions)
direct - 使用直通路徑方式匯入 (預設FALSE) 某日讀了一篇文章，說是如果選擇匯入方式為true或是y，則會繞過了SGA，直接寫datafile，而且是直接在HWM以上寫。這就導致了HWM會被不斷抬高，這樣即使後來delete掉了這批資料，HWM依然沒有被降下來。HWM不斷被抬高的直接表現就是在做全表掃描的時候會越來越慢
parfile -- parameter file: name of file that contains parameter specifications
parallel -- 並行匯入 (預設FALSE)
file -- File to allocate extents from
與bindsize成對使用，其中較小者會自動調整到較大者
sqlldr先計算單條記錄長度，乘以rows，如小於bindsize，不會試圖擴張rows以填充bindsize；如超出，則以bindsize為準。
external_table
-- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(預設NOT_USED)
columnarrayrows
-- Number of rows for direct path column array(預設5000)
streamsize -- Size of direct path stream buffer in bytes(預設256000)
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(預設7200)
date_cache -- size (in entries) of date conversion cache(預設1000)
注意：有兩種方式可以指定命令列引數：透過位置或者透過關鍵字。前者的例子：'sqlldr scott/tiger foo'；
後者的例子：'sqlldr control=foo userid=scott/tiger'；
不能前面使用關鍵字指定後面透過位置制定的混合方式；
比如：'sqlldr scott/tiger control=foo logfile=log' 是允許的，
但'sqlldr scott/tiger control=foo log'不允許。
為清楚起見最好所有命令列引數都用關鍵字指定。
控制檔案：
一個控制命令的指令碼檔案，通常以ctl結尾，內容如下：
LOAD DATA
INFILE 't.dat' 要匯入的檔案
// INFILE 'tt.date' 匯入多個檔案
// INFILE * 表示要匯入的內容就在control檔案裡 下面的BEGINDATA後面就是匯入的內容
INTO TABLE table_name 指定裝入的表
BADFILE 'c:\bad.txt' 可選，指定壞檔案地址,預設在當前目錄下生成與原檔名一致的.bad檔案
************* 以下是4種裝入表的方式
APPEND 原先的表有資料 就加在後面
INSERT 裝載空表 如果原先的表有資料 sqlloader會停止 預設值
REPLACE 原先的表有資料 原先的資料會全部刪除
TRUNCATE 指定的內容和replace的相同 會用truncate語句刪除現存資料
************* 指定分隔符
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// TERMINATED BY WRITESPACE 以空白分割
TRAILING NULLCOLS 表的欄位沒有對應的值時允許為空
************* 下面是表的欄位
(
col_1 , col_2 ,col_filler FILLER // FILLER 關鍵字 此列的數值不會被裝載
// 如: lg,lg,not 結果 lg lg
)
如果沒宣告FIELDS TERMINATED BY ',' 時，可以用下面兩種方式實現同樣功能：
1．為每一列指定分隔符
(
col_1 [interger external] TERMINATED BY ',' ,
col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
)
2．用位置告訴欄位裝載資料
(
col_1 position(1:2),
col_2 position(3:10),
col_3 position(*:16), // 這個欄位的開始位置在前一欄位的結束位置
col_4 position(1:16),
col_5 position(3:10) char(8) // 指定欄位的型別
)
BEGINDATA 對應開始的 INFILE * 要匯入的內容就在control檔案裡
10,Sql,what
20,lg,show
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-718049/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用sqlldr匯入文字資料到oracleSQLOracle
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- 資料匯入SQLLDRSQL
- Oracle工具之sqlldr的使用--如何將文字檔案或Excel中的資料匯入資料庫OracleSQLExcel資料庫
- oracle sqlldr匯入OracleSQL
- 使用sqlloader向oracle匯入文字資料SQLOracle
- 用Oracle sqlldr匯入文字檔案TXT 總結OracleSQL
- Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- sqlldr 匯入資料範例SQL
- 文字檔案用sqlldr工具匯入到oracel資料庫中SQL資料庫
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- sqlldr批量匯入匯出資料測試SQL
- sqlldr批次匯入匯出資料測試SQL
- 通過SQLLDR匯入LOB資料SQL
- 關於 Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- 【匯入匯出】sqlldr 匯入含有內嵌換行符的資料SQL
- ociuldr sqlldr 文字匯出工具SQL
- 【匯入匯出】sqlldr 匯入案例SQL
- Oracle使用dump匯入資料Oracle
- 大文字資料，匯入匯出到資料庫資料庫
- Oracle使用資料泵匯出匯入表Oracle
- sqlldr的例子SQL
- Redis批量匯入文字資料Redis
- sqlldr匯入資料中文亂碼SQL
- Oracle使用sqlldr load匯入txt出現中文亂碼OracleSQL
- 使用FSO把文字資訊匯入資料庫 (轉)資料庫
- sqlldr 匯入重複資料導致PK INDEX失效SQLIndex
- Oracle 資料匯入匯出Oracle
- oracle資料匯入匯出Oracle
- 使用sqlldr匯入日期格式欄位的問題SQL
- 使用Oracle SQL Developer匯入Excel資料OracleSQLDeveloperExcel
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- 【sqlldr載入資料】SQL
- sqlldr匯入資料包錯 Field in data file exceeds maximum lengthSQL
- 使用Oracle 的 imp ,exp 命令實現資料的匯入匯出Oracle
- 透過sqlldr匯入到sys使用者SQL
- 通過sqlldr匯入到sys使用者SQL
- 【SQL*Loader】sqlldr匯入SQL