關于Oracle中sqlldr的用法大全
在 Oracle 數據庫中，我們通常在不同數據庫的表間記錄進行復制或遷移時會用以下幾種方法：
1. A 表的記錄導出為一條條分號隔開的 insert 語句，然后執行插入到 B 表中
2. 建立數據庫間的 dblink，然后用 create tableB as select * fromwhere ...，或 insert intoB select * fromwhere ...
3. exp A表，再 imp 到 B 表，exp 時可加查詢條件
4. 程序實現 select from A ..，然后 insert into B ...，也要分批提交
5. 再就是本篇要說到的 Sql Loader(sqlldr) 來導入數據，效果比起逐條 insert 來很明顯
第 1 種方法在記錄多時是個噩夢，需三五百條的分批提交，否則客戶端會死掉，而且導入過程很慢。如果要不產生 REDO 來提高 insert into 的性能，就要下面那樣做：
alter table B nologging; insert /* +APPEND */ into B(c1,c2) values(x,xx); insert /* +APPEND */ into B select * from A@dblink where .....;前面簡述了 Oracle 中數據導入導出的各種方法，我想一定還有更高明的。下面重點講講 Oracle 的 Sql Loader (sqlldr) 的用法。
在命令行下執行 Oracle的 sqlldr 命令，可以看到它的詳細參數說明，要著重關注以下幾個參數：
userid-- Oracle 的 username/password[@servicename]control-- 控制文件，可能包含表的數據log -- 記錄導入時的日志文件，默認為 控制文件(去除擴展名).logbad -- 壞數據文件，默認為 控制文件(去除擴展名).baddata -- 數據文件，一般在控制文件中指定。用參數控制文件中不指定數據文件更適于自動操作errors -- 允許的錯誤記錄數，可以用他來控制一條記錄都不能錯rows -- 多少條記錄提交一次，默認為 64skip -- 跳過的行數，比如導出的數據文件前面幾行是表頭或其他描述其他參數用法示例：用例子來演示 sqlldr 的使用，有兩種使用方法：
1. 只使用一個控制文件，在這個控制文件中包含數據
2. 使用一個控制文件(作為模板) 和一個數據文件
第一種：一般為了利于模板和數據的 分離，以及程序的不同分工會使用第二種方式，所以先來看這種用法。數據文件可以是 CSV 文件或者以其他分割符分隔的，數據文件可以用 PL/SQL Developer 或者 Toad 導出，也可以。另外，用 Toad 還能直接生成包含數據的控制文件。
首先，假定有這么一個表 users，并插入五條記錄：
create table users( user_id number, --用戶 ID user_name varchar2(50), --用戶名 login_times number, --登陸次數 last_login date --最后登錄日期 )數據：
insert?into?users?values(1,'Unmi',3,sysdate);??insert?into?users?values(2,NULL,5,to_date('2008-10-15','YYYY-MM-DD'));??insert?into?users?values(3,'隔葉黃鶯 ',8,to_date('2009-01-02','YYYY-MM-DD'));??insert?into?users?values(4,'Kypfos',NULL,NULL);??insert?into?users?values(5,'不知秋 ',1,to_date('2008-12-23','YYYY-MM-DD'));第二種：使用一個控制文件(作為模板) 和一個數據文件
1) 建立數據文件，我們這里用 PL/SQL Developer 導出表 users 的記錄為 users_data.csv 文件，內容如下：
' ','USER_ID','USER_NAME','LOGIN_TIMES','LAST_LOGIN' '1','1','Unmi','3','2009-1-5 20:34:44' '2','2','','5','2008-10-15' '3','3','隔葉黃鶯','8','2009-1-2' '4','4','Kypfos','','' '5','5','不知秋','1','2008-12-23'2) 建立一個控制文件 users.ctl，內容如下：
OPTIONS (skip=1,rows=128) -- sqlldr 命令顯示的 選項可以寫到這里邊來,skip=1 用來跳過數據中的第一行 LOAD DATA INFILE 'users_data.csv' --指定外部數據文件，可以寫多 個 INFILE 'another_data_file.csv' 指定多個數據文件 --這里還可以使 用 BADFILE、DISCARDFILE 來指定壞數據和丟棄數據的文件， truncate --操作類型，用 truncate table 來清除表中原有 記錄 INTO TABLE users -- 要插入記錄的表 Fields terminated by ',' -- 數據中每行記錄用 ',' 分隔 Optionally enclosed by ''' -- 數據中每個字段用 ''' 框起，比如字段中有 ',' 分隔符時 trailing nullcols --表的字段沒有對應的值時允 許為空 ( virtual_column FILLER, --這是一個虛擬字段，用來跳 過由 PL/SQL Developer 生成的第一列序號 user_id number, --字段可以指定類型，否則認 為是 CHARACTER 類型, log 文件中有顯示 user_name, login_times, last_login DATE 'YYYY-MM-DD HH24:MI:SS' -- 指定接受日期的格式，相當用 to_date() 函數轉換 )說明：在操作類型 truncate 位置可用以下中的一值：
1) insert --為缺省方式，在數據裝載開始時要求表為空2) append --在表中追加新記錄3) replace --刪除舊記錄(用 delete from table 語句)，替換成新裝載的記錄4) truncate --刪除舊記錄(用 truncate table 語句)，替換成新裝載的記錄
3) 執行命令：
sqlldr control=users.ctl在 dbservice 指示的數據庫的表 users 中記錄就和數據文件中的一樣了。
執行完 sqlldr 后希望能留意一下生成的幾個文件，如 users.log 日志文件、users.bad 壞數據文件等。特別是要看看日志文件，從中可讓你更好的理解 Sql Loader，里面有對控制文件的解析、列出每個字段的類型、加載記錄的統計、出錯原因等信息。
第一種方式，只使用一個控制文件 在這個控制文件中包含數據
1) 把 users_data.cvs 中的內容補到 users.ctl 中，并以 BEGINDATA 連接，還要把 INFILE "users_data.csv" 改為 INFILE *。同時為了更大化的說明問題，把數據處理了一下。此時，完整的 users.ctl 文件內容是：
OPTIONS (skip=1,rows=128) -- sqlldr 命令顯示的 選項可以寫到這里邊來,skip=1 用來跳過數據中的第一行 LOAD DATA INFILE * -- 因為數據同控制文件在一 起，所以用 * 表示 append -- 這里用 了 append 來操作，在表 users 中附加記錄 INTO TABLE users when LOGIN_TIMES<>'8' -- 還可以用 when 子 句選擇導入符合條件的記錄 Fields terminated by ',' trailing nullcols ( virtual_column FILLER, --跳過 由 PL/SQL Developer 生成的第一列序號 user_id 'user_seq.nextval', --這一列直接取序列的下一值，而不用數據中提供的值 user_name ''Hi '||upper(:user_name)',--,還能用SQL函數或運算對數據進行加工處理 login_times terminated by ',', NULLIF(login_times='NULL') --可為列單獨指定分隔符 last_login DATE 'YYYY-MM-DD HH24:MI:SS' NULLIF (last_login='NULL') -- 當字段為'NULL'時就是 NULL ) BEGINDATA --數據從這里開始 ,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN 1,1,Unmi,3,2009-1-5 20:34 2,2,Fantasia,5,2008-10-15 3,3,隔葉黃 鶯,8,2009-1-2 4,4,Kypfos,NULL,NULL 5,5,不知 秋,1,2008-12-232) 執行一樣的命令：
sqlldr control=users.ctl比如，在控制臺會顯示這樣的信息：
C:\>sqlldr control=users.ctlSQL*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.達到提交點，邏輯記錄計數4達到提交點，邏輯記錄計數5
上面的控制文 件包含的內容比較復雜(演示目的)，請根據注釋理解每個參數的意義。還能由此發掘更多用法。
關于SQL *Loader 的性能與并發操作ROWS 的默認值為 64，你可以根據實際指定更合適的 ROWS 參數來指定每次提交記錄數。(體驗過在 PL/SQL Developer 中一次執行幾條條以上的 insert 語句的情形嗎？)常規導入可以通過使用 INSERT語句來導入數據。Direct導入可以跳過數據庫的相關邏輯(DIRECT=TRUE)，而直接將數據導入到數據文件中，可以提高導入數據的 性能。當然，在很多情況下，不能使用此參數(如果主鍵重復的話會使索引的狀態變成UNUSABLE!)。通過指定 UNRECOVERABLE選項，可以關閉數據庫的日志(是否要 alter table table1 nologging 呢?)。這個選項只能和 direct 一起使用。對于超大數據文件的導入就要用并發操作了，即同時運行多個導入任務：sqlldr userid=/ control=result1.ctl direct=true parallel=truesqlldr userid=/ control=result2.ctl direct=true parallel=truesqlldr userid=/ control=result2.ctl direct=true parallel=true當加載大量數據時（大約超過10GB），最好抑制日志的產生：
ALTER TABLE RESULTXT nologging;這樣不產生REDO LOG，可以提高效率。然后在 CONTROL 文件中 load data 上面加一行：unrecoverable， 此選項必須要與DIRECT共同應用。
在并發操作時，ORACLE聲稱可以達到每小時處理100GB數據的能力！其實，估計能到 1－10G 就算不錯了，開始可用結構 相同的文件，但只有少量數據，成功后開始加載大量數據，這樣可以避免時間的浪費。
（注意：一般只能用ASCII碼形式，切記要轉換編碼，不然導入數據為空）
到此這篇關于關于Oracle中sqlldr的用法大全的文章就介紹到這了,更多相關Oracle的sqlldr用法內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！
網公網安備