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