sqlldr控制文件詳解

❶ oraclesqlloader全攻略

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

C:>sqlldr

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

Copyright (c) Oracle Corporation All rights reserved

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

有效的關鍵字:

userid ORACLE username/passwordcontrol Control file namelog Log file namebad Bad file namedata Data file namediscard Discard file namediscardmax 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 beeen direct path data saves（默認: 常規路徑 所有直接路徑）bindsize Size of conventional path bind array in bytes(默認 )silent Suppress messages ring run (header feedback errors discards partitions)direct use direct path (默認FALSE)parfile parameter file: name of file that contains parameter specificationsparallel do parallel load (默認FALSE)file File to allocate extents fromskip_unusable_indexes disallow/allow unusable indexes or index partitions(默認FALSE)skip_index_maintenance do not maintain indexes mark affected indexes as unusable(默認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 pathresumable enable or disable resumable for current session(默認FALSE)resumable_name text string to help identify resumable statementresumable_timeout wait time (in seconds) for RESUMABLE(默認 )date_cache size (in entries) of date conversion cache(默認 )

PLEASE NOTE: 命令行參數可以由位置或關鍵字指定 前者的例子是 sqlloadscott/tiger foo ; 後一種情況的一個示例是 sqlldr control=foouserid=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: 用記事本編輯 控制文件 input ctl 內容如下 load data 控制文件標識 infile test txt 要輸入的數據文件名為test txt append into table test 向表test中追加記錄 fields terminated by X 欄位終止於X 是一個製表符（TAB） (id username password sj) 定義列對應順序 a insert 為預設方式 在數據裝載開始時要求表為空 b append 在表中追加新記錄 c replace 刪除舊記錄 替換成新裝載的記錄 d truncate 同上 在DOS窗口下使用SQL*Loader命令實現數據的輸入 C:>sqlldr userid=system/manager control=input ctl 默認日誌文件名為 input log 默認壞記錄文件為 input bad 還有一種方法可以把EXCEL文件另存為CSV（逗號分隔）(* csv) 控制文件就改為用逗號分隔 LOAD DATA INFILE d:car csv APPEND INTO TABLE t_car_temp FIELDS TERMINATED BY (phoneno vip_car)

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

控制文件test ctl的內容 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 DATAINFILE *BADFILE C:Documents and SettingsJackey桌面WMCOUNTRY BAD DISCARDFILE C:Documents and SettingsJackey桌面WMCOUNTRY DSC INSERT INTO TABLE EMCCOUNTRYFields 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 ; Azerjan ; ; ; / / : : ;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=test ctl

part ii

SQL*Loader是Oracle資料庫導入外部數據的一個工具 它和DB 的Load工具相似 但有更多的選擇 它支持變化的載入模式 可選的載入及多表載入 如何使用 SQL*Loader 工具我們可以用Oracle的sqlldr工具來導入數據 例如:sqlldr scott/tiger control=loader ctl控制文件(loader ctl) 將載入一個外部數據文件（含分隔符) loader ctl如下:load datainfile c:datamydata csv into table empfields terminated by optionally enclosed by ( empno empname sal deptno )mydata csv 如下: Scott Tiger Frank Naude 下面是一個指定記錄長度的示例控制文件 * 代表數據文件與此文件同名 即在後面使用BEGINDATA段來標識數據 load datainfile *replaceinto table departments( dept position ( : ) char( ) deptname position ( : ) char( ))begindataCOSC PUTER SCIENCEENGL ENGLISH LITERATUREMATH MATHEMATICSPOLY POLITICAL SCIENCEUnloader這樣的工具Oracle 沒有提供將數據導出到一個文件的工具 但是 我們可以用SQL*Plus的select 及 format 數據來輸出到一個文件 set echo off newpage space pagesize feed off head off trimspool onspool oradata txtselect col || || col || || col from tab where col = XYZ ;spool off另外 也可以使用使用 UTL_FILE PL/SQL 包處理:rem Remember to update initSID ora utl_file_dir= c:oradata parameterdeclarefp utl_file file_type;beginfp := utl_file fopen( c:oradata tab txt w );utl_file putf(fp %s %s TextField );utl_file fclose(fp);end;/當然你也可以使用第三方工具 如SQLWays TOAD for Quest等 載入可變長度或指定長度的記錄如 LOAD DATAINFILE *INTO TABLE load_delimited_dataFIELDS TERMINATED BY OPTIONALLY ENCLOSED BY TRAILING NULLCOLS( data data )BEGINDATA AAAAAAAAAA A B C D 下面是導入固定位置（固定長度）數據示例 LOAD DATAINFILE *INTO TABLE load_positional_data( data POSITION( : ) data POSITION( : ))BEGINDATA AAAAAAAAAA BBBBBBBBBB跳過數據行 可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據 如 LOAD DATAINFILE *INTO TABLE load_positional_dataSKIP ( data POSITION( : ) data POSITION( : ))BEGINDATA AAAAAAAAAA BBBBBBBBBB導入數據時修改數據 在導入數據到資料庫時 可以修改數據 注意 這僅適合於常規導入 並不適合 direct導入方式 如 LOAD DATAINFILE *INTO TABLE modified_data( rec_no my_db_sequence nextval 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 DATAINFILE mail_orders txt BADFILE bad_orders txt APPENDINTO TABLE mailing_listFIELDS 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 DATAINFILE *REPLACEINTO TABLE empWHEN empno != ( empno POSITION( : ) INTEGER EXTERNAL ename POSITION( : ) CHAR deptno POSITION( : ) CHAR mgr POSITION( : ) INTEGER EXTERNAL)INTO TABLE projWHEN projno != ( projno POSITION( : ) INTEGER EXTERNAL empno POSITION( : ) INTEGER EXTERNAL)導入選定的記錄 如下例 ( ) 代表第一個字元 ( : ) 代表 到 之間的字元:LOAD DATAINFILE mydata dat BADFILE mydata bad DISCARDFILE mydata dis APPENDINTO TABLE my_selective_tableWHEN ( ) <> H and ( ) <> T and ( : ) = (region CONSTANT service_key POSITION( : ) INTEGER EXTERNAL call_b_no POSITION( : ) CHAR)導入時跳過某些欄位 可用 POSTION(x:y) 來分隔數據 在Oracle i中可以通過指定 FILLER 欄位實現 FILLER 欄位用來跳過 忽略導入數據文件中的欄位 如 LOAD DATATRUNCATE INTO TABLE T FIELDS TERMINATED BY ( field field FILLER field )導入多行記錄 可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:CONCATENATE: use when SQL*Loader should bine 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) 而直接將數據導入到數據文件中 lishixin/Article/program/Oracle/201311/17401

❷ Oracle數據導入有哪些常用命令

Oracle數據導入的常用命令並不直接包含在提供的參考信息中，但基於Oracle資料庫的操作，與數據導入相關的常用命令主要包括以下幾種：

1. 使用SQL*Loader導入數據

   • SQL*Loader是一個強大的工具，用於從外部文件中高速載入數據到Oracle資料庫中。
   • 常用命令格式：sqlldr userid=username/password control=controlfile.ctl log=logfile.log
     • userid：指定Oracle資料庫的用戶名和密碼。
     • control：指定包含導入指令的控制文件。
     • log：指定日誌文件路徑，用於記錄導入過程中的詳細信息。

2. 使用Data Pump導入數據

   • Data Pump是Oracle提供的一種高效的數據導入導出工具，相比傳統的exp/imp工具，它提供了更好的性能和更多的功能。
   • 導入命令：impdp userid=username/password directory=dir_name mpfile=mpfile.dmp logfile=logfile.log
     • userid：指定Oracle資料庫的用戶名和密碼。
     • directory：指定Oracle資料庫中的一個目錄對象，該對象指向文件系統中的一個路徑。
     • mpfile：指定導出的轉儲文件名。
     • logfile：指定日誌文件路徑。

3. 使用INSERT語句導入數據

   • 對於小規模的數據集，可以直接使用INSERT語句將數據插入到表中。
   • 例如：INSERT INTO table_name VALUES ;

4. 使用外部表導入數據

   • 外部表允許你像查詢普通表一樣查詢外部數據文件，這對於需要定期從外部數據源載入數據的情況非常有用。
   • 創建外部表的命令涉及指定數據文件的路徑、格式以及表的結構。

注意：上述命令中的參數和選項可能因Oracle資料庫版本的不同而有所差異。在實際操作中，請參考你所使用的Oracle資料庫版本的官方文檔。

此外，雖然參考信息中提供了用戶管理、表空間管理等命令，但這些命令與數據導入不直接相關，因此在回答此問題時未予包含。

❸ oracle 怎麼用sqlldr導入dat文件

1.建表:

SQL> create table tb_boxnumber(boxnumber varchar2(30),Status varchar2(8));

Table created.

SQL>

2.創建一個控制文件:

oracle@linux:~> vi 123.ctl
load data
append
into table tb_boxnumber //定義要導入的表
fields terminated ','
trailing nullcols

truncate //這個地方可以對表進行truncate，根據自己情況是否使用
(boxnumber) //定義導入的欄位

3.使用sqlldr導入數據:

使用ftp工具，把你要導入的數據上傳到伺服器上，我測試過導入txt和dat的文件格式，使用如下命令:

oracle@linux:~> sqlldr
zxdbp_test/zxdbp_test@zxin control=123.ctl data=1.dat

其中1.dat是我上傳的文件

❹ 簡述資料庫導入OracleSQL*Loader指南

SQL*Loader是Oracle資料庫導入外部數據的一個工具 它和DB 的Load工具相似 但有更多的選擇 它支持變化的載入模式 可選的載入及多表載入

如何使用 SQL*Loader 工具

我們可以用Oracle的sqlldr工具來導入數據 例如:

sqlldr scott/tiger control=loader ctl

控制文件(loader ctl) 將載入一個外部數據文件（含分隔符)loader ctl如下:

load data infile c:datamydata csv into table emp fields terminated by optionally enclosed by ( empno empname sal deptno )

mydata csv 如下:

Scott Tiger Frank Naude

下面是一個指定記錄長度的示例控制文件 * 代表數據文件與此文件同名 即在後面使用BEGINDATA段來標識數據

load data infile * replace into table departments ( dept position ( : ) char( ) deptname position ( : ) char( ) ) begindata COSC PUTER 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 oradata txt select col || || col || || col from tab where col = XYZ ; spool off

另外 也歷孝可以使用使用 UTL_FILE PL/SQL 包處困虛理:

rem Remember to update initSID ora utl_file_dir= c:oradata parameter declare fp utl_file file_type; begin fp := utl_file fopen( c:oradata tab txt w ); utl_file putf(fp %s %s TextField ); utl_file fclose(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 A B C D 下面是導入固定位置（固定長度）數據示例 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_sequence nextval 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_orders txt BADFILE bad_orders txt 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 mydata dat BADFILE mydata bad DISCARDFILE mydata dis 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) 來分隔數據在Oracle i中可以通過指定FILLER欄位實現 FILLER 欄位用來跳過 忽略導入數據文件中的欄位 如

LOAD DATA TRUNCATE INTO TABLE T FIELDS TERMINATED BY ( field field FILLER field )

導入多行記錄

可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:

CONCATENATE: use when SQL*Loader should bine 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 oneEgby having a # character in column

SQL*Loader 數據的提交

一般情況下是在導入數據文件數據後提交的 也可以通過指定 ROWS= 參數來指定每次提交記錄數

提高 SQL*Loader的性能

( ) 一個簡單而容易忽略的問題是 沒有對導入的表使用任何索引和/或約束(主鍵) 如果這樣做 甚至在使用ROWS=參數時 會很明顯降低資料庫導入性能

( ) 可以添加 DIRECT=TRUE來提高導入數據的性能 當然 在很多情況下 不能使用此參數

( ) 通過指定UNRECOVERABLE選項 可以關閉資料庫的日誌 這個選項只能和 direct 一起使用

( ) 可以同時運行多個導入任務

常規導入與direct導入方式的區別

lishixin/Article/program/Oracle/201311/18244