sqlloader 导入数据的一点经验教训(最后附我的导入过程)

最新推荐文章于 2024-08-29 04:08:20 发布
最新推荐文章于 2024-08-29 04:08:20 发布
阅读量2w 收藏 47
点赞数 4
分类专栏： oracle-11g sqlldr 文章标签： 数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/louisjh/article/details/78871225
版权

sqlloader 导入数据，8.6个G,以nologging方式导入，大概用了25分钟（与服务器性能、插入语句等因素有关）数据文件为csv格式，切分5个CSV文件插入对应的分区表。导入方法都是按标准语法写（参考sqlldr使用方法和工具简介），需要特别注意的是，导入过程关闭归档是一个提升速度的好方法，如果服务器性能不佳的话，甚至可以在内网环境中找另外一台服务器远程sqlldr方式导入，经本人实践效果很不错（A服务器读CSV经内网B服务器写，也算是读写分离），还有就是导入过程中还是会ORACLE会产生大量的中间数据，所以要注意观察目标tablespace的FREE空间以及其所对应的默认临时表TEMP空间大小，不要被撑爆导致导入失败。以下是我参照的使用方法贴出来给大家做个参考。
sqlldr使用方法和工具简介
Oracle 的SQLLOADER可以将外部数据加载到数据库表中。下面是SQLLOADER的基本特点：
1）能装入不同数据类型文件及多个数据文件的数据
2）可装入固定格式，自由定界以及可度长格式的数据
3）可以装入二进制，压缩十进制数据
4）一次可对多个表装入数据
5）连接多个物理记录装到一个记录中
6）对一单记录分解再装入到表中
7）可以用 数对制定列生成唯一的KEY
8）可对磁盘或 磁带数据文件装入制表中
9）提供装入错误报告
10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
11）可以用filler关键字，不装载某列：field2 FILLER,
12）可以用 “SKIP n” 关键字来指定导入时可以跳过多少行数据
13）可以将多行当着一行记录

1.2控制文件
控制文件是用一种语言写的文本文件，这个文本文件能被SQLLOADER识别。SQLLOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成：
l 全局选件，行，跳过的记录数等；
l INFILE子句指定的输入数据；
l 数据特性说明。
1.3输入文件
对于 SQLLoader, 除控制文件外就是输入数据。SQLLoader可从一个或多个指定的文件中读出数据。如果数据是在控制文件中指定，就要在控制文件中写成 INFILE * 格式。当数据固定的格式（长度一样）时且是在文件中得到时，要用INFILE “fix n”
load data
infile ‘example.dat’ “fix 11”
into table example
fields terminated by ‘,’ optionally enclosed by ‘"’
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, “pqrs”,
0005,uvwx,
当数据是可变格式（长度不一样）时且是在文件中得到时，要用INFILE “var n”。如：
load data
infile ‘example.dat’ “var 3”
into table example
fields terminated by ‘,’ optionally enclosed by ‘"’
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,
1.4坏文件
坏文件包含那些被SQLLoader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQLLoader命令的BADFILE 参数来给定。
1.5日志文件及日志信息
当SQLLoader 开始执行后，它就自动建立 日志文件。日志文件包含有加载的总结，加载中的错误信息等。
控制文件语法
控制文件的格式如下：
OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { ‘string’ | X ‘hex’ } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]…]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] ‘character’ } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]‘charcter’]
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,…]
)
[INTO TABLE…]
[BEGINDATA]
1）要加载的数据文件：
1．INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件。
2．STRAM 表示一次读一个字节的数据。新行代表新物理记录（逻辑记录可由几个物理记录组成）。
3．RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
3． FIXED length 要读的记录长度为length字节，
4． VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。缺伤为8k字节。
5． BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
6． DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
7． DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。
2）加载的方法：
1．APPEND 给表添加行。
2．INSERT 给空表增加行（如果表中有记录则退出）。
3．REPLACE 先清空表再加载数据。
4． RECLEN 用于两种情况，1）SQLLDR不能自动计算记录长度，2）或用户想看坏文件的完整记录时。对于后一种，Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录，则可以将整条记录写到坏文件中。
3）指定最大的记录长度：
1． CONCATENATE 允许用户设定一个整数，表示要组合逻辑记录的数目。
4）建立逻辑记录：
1．THIS 检查当前记录条件，如果为真则连接下一个记录。
2．NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。
2． Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。如：continueif next(1-3)=‘WAG’ 或continueif next(1-3)=X’0d03if’
5）指定要加载的表：
1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE类似。用来检查记录的情况，如：when(3-5)=‘SSM’ and (22)=’"
6）介绍并括起记录中的字段：
1． FIELDS给出记录中字段的分隔符，FIELDS格式为：
FIELDS [TERMIALED [BY] {WHITESPACE | [X] ‘charcter’} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]‘charcter’ ]
TERMINATED 读完前一个字段即开始读下一个字段直到介绍。
WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符，可以用单引号括起，如X’1B’等。
OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。
ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ，则它们的顺序决定计算的顺序。
7）定义列：
column 是表列名。列的取值可以是：
BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
CONSTANT 表示赋予常数。
SEQUENCE 表示序列可以从任意序号开始，格式为：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 给出列在逻辑记录中的位置。可以是绝对的，或相对前一列的值。格式为：
POSITION ( {start[end] | * [+integer] } )
Start 开始位置

  • 表示前字段之后立刻开始。
  • 从前列开始向后条的位置数。
    8）定义数据类型：
    可以定义14种数据类型：
    CHAR
    DATE
    DECIMAL EXTERNAL
    DECIMAL
    DOUBLE
    FLOAT
    FLOAT EXTERNAL
    GRAPHIC EXTERNAL
    INTEGER
    INTEGER EXTERNAL
    SMALLINT
    VARCHAR
    VARGRAPHIC
    1．字符类型数据
    CHAR[ (length)] [delimiter]
    length缺省为 1.
    2.日期类型数据
    DATE [ ( length)][‘date_format’ [delimiter]
    使用to_date函数来限制。
    3．字符格式中的十进制
    DECIMAL EXTERNAL [(length)] [delimiter]
    用于常规格式的十进制数（不是二进制=> 一个位等于一个bit）。
    4．压缩十进制格式数据
    DECIMAL （digtial [,divcision]）
    5．双精度符点二进制
    DOUBLE
    6．普通符点二进制
    FLOAT
    7．字符格式符点数
    FLOAT EXTERNAL [ (length) ] [delimiter]
    8．双字节字符串数据
    GRAPHIC [ (legth)]
    9．双字节字符串数据
    GRAPHIC EXTERNAL[ (legth)]
    10．常规全字二进制整数
    INTEGER
    11．字符格式整数
    INTEGER EXTERNAL
    12．常规全字二进制数据
    SMALLINT
    13．可变长度字符串
    VARCHAR
    14．可变双字节字符串数据
    VARGRAPHIC
    2.2写控制文件CTL
    1． 各数据文件的文件名;
    2．各数据文件格式;
    3．各数据文件里各数据记录字段的属性；
    4．接受数据的ORACLE表列的属性；
    5．数据定义；
    6．其它
    数据文件的要求：
    数据类型的指定
    CHAR 字符型
    INTEGER EXTERNAL 整型
    DECIMAL EXTERNAL 浮点型
    3.1数据文件的内容
    可以在OS下的一个文件；或跟在控制文件下的具体数据。数据文件可以是：
    1、 二进制与字符格式：LOADER可以把二进制文件读（当成字符读）列表中
    2、 固定格式：记录中的数据、数据类型、 数据长度固定。
    3、 可变格式：每个记录至少有一个可变长数据字段，一个记录可以是一个连续的字符串。
    数据段的分界（如姓名、年龄）如用“，”作字段的 分 ；用，"’作数据
    括号等
    4、 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录，记录文件运行情况文件：包括以下内容：
    1、 运行日期：软件版本号
    2、 全部输入，输出文件名；对命令行的展示信息，补充信息，
    3、 对每个装入信息报告：如表名，装入情况；对初始装入， 加截入或更新装
    入的选择情况，栏信息
    4、 数据错误报告：错误码；放弃记录报告
    5、 每个装X报告：装入行；装入行数，可能跳过行数；可能拒绝行数；可能放
    弃行数等
    6、 统计概要：使用空间（包大小，长度）；读入记录数，装入记录数，跳过记
    录数；拒绝记录数，放弃记录数；运行时间等。
    sql load的一点小总结
    sqlldr userid=lgone/tiger control=a.ctl
    LOAD DATA
    INFILE ‘t.dat’ // 要导入的文件
    // INFILE ‘tt.date’ // 导入多个文件
    // INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

INTO TABLE table_name // 指定装入的表
BADFILE ‘c:\bad.txt’ // 指定坏文件地址

************* 以下是4种装入表的方式
APPEND // 原先的表有数据 就加在后面
// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
// REPLACE // 原先的表有数据 原先的数据会全部删除
// TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据

************* 指定的TERMINATED可以在表的开头 也可在表的内部字段部分
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
// 装载这种数据: 10,lg,""“lg”"",“lg,lg”
// 在表中结果: 10 lg “lg” lg,lg
// TERMINATED BY X ‘09’ // 以十六进制格式 ‘09’ 表示的
// TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg

TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空

************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载
// 如: lg,lg,not 结果 lg lg
)
// 当没声明FIELDS TERMINATED BY ‘,’ 时
// (
// col_1 [interger external] TERMINATED BY ‘,’ ,
// col_2 [date “dd-mon-yyy”] TERMINATED BY ‘,’ ,
// col_3 [char] TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘lg’
// )
// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据
// (
// 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

=====================================================================================
注意begindata后的数值前面不能有空格

1 ***** 普通装载
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,""“USA”""
20,Accounting,“Virginia,USA”
30,Consulting,Virginia
40,Finance,Virginia
50,“Finance”,"",Virginia // loc 列将为空
60,“Finance”,Virginia // loc 列将为空

2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x’09’ 的情况
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
– FIELDS TERMINATED BY x’09’
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia

3 ***** 指定不装载那一列
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
( DEPTNO,
FILLER_1 FILLER, // 下面的 “Something Not To Be Loaded” 将不会被装载
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,“Virginia,USA”

4 ***** position的列子
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(:16), // 这个字段的开始位置在前一字段的结束位置
LOC position(:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA

5 ***** 使用函数 日期的一种表达 TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
// 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME “upper(:dname)”, // 使用函数
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’, // 日期的一种表达方式 还有’dd-mon-yyyy’ 等
ENTIRE_LINE “:deptno||:dname||:loc||:last_updated”
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

6 ***** 使用自定义的函数 // 解决的时间问题
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);

l_fmts fmtArray := fmtArray( ‘dd-mon-yyyy’, ‘dd-month-yyyy’,
‘dd/mm/yyyy’,
‘dd/mm/yyyy hh24:mi:ss’ );
l_return date;
begin
for i in 1 … l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;

if ( l_return is null )
then
l_return :=
new_time( to_date(‘01011970’,‘ddmmyyyy’) + 1/24/60/60 *
p_string, ‘GMT’, ‘EST’ );
end if;

return l_return;
end;
/

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )” // 使用自定义的函数
)
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date

7 ***** 合并多行记录为一行记录
LOAD DATA
INFILE *
concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
BEGINDATA
10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 这列子用 continueif list="," 也可以
告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行

LOAD DATA
INFILE *
continueif this(1:1) = ‘-’ // 找每行的开始是否有连接字符 - 有就把下一行连接为一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ‘,’
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED date ‘dd/mm/yyyy’
)
BEGINDATA // 但是好象不能象右面的那样使用
-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001

8 ***** 载入每行的行号

load data
infile *
into table t
replace
( seqno RECNUM //载入每行的行号
text Position(1:1024))
BEGINDATA
fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1
fasdjfasdfl // 此行为 2 …

9 ***** 载入有换行符的数据
注意: unix 和 windows 不同& /n
< 1 > 使用一个非换行符的字符
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )”,
COMMENTS “replace(:comments,’\n’,chr(10))” // replace 的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia

< 2 > 使用fix属性
LOAD DATA
INFILE demo17.dat “fix 101”
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )”,
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia

// 这样装载会把换行符装入 下面的方法就不会 但要求数据的格式不同

LOAD DATA
INFILE demo18.dat “fix 101”
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )”,
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,“This is the Sales
Office in Virginia”
20,Accounting,Virginia,13/04/2001,“This is the Accounting
Office in Virginia”
30,Consulting,Virginia,14/04/2001 12:02:02,“This is the Consulting
Office in Virginia”
40,Finance,Virginia,987268297,“This is the Finance
Office in Virginia”

< 3 > 使用var属性
LOAD DATA
INFILE demo19.dat “var 3”
// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )”,
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia

< 4 > 使用str属性
// 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)

此列中记录是以 a|\r\n 结束的
select utl_raw.cast_to_raw(’|’||chr(13)||chr(10)) from dual;
结果 7C0D0A

LOAD DATA
INFILE demo20.dat “str X’7C0D0A’”
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(DEPTNO,
DNAME “upper(:dname)”,
LOC “upper(:loc)”,
LAST_UPDATED “my_to_date( :last_updated )”,
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia|
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia|

==============================================================================
象这样的数据 用 nullif 子句

10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Sdivad over three meals.
 
id position(1:3) nullif id=blanks // 这里可以是blanks 或者别的表达式
// 下面是另一个列子 第一行的 1 在数据库中将成为 null
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(n position(1:2) integer external nullif n='1',
v position(3:8)
)
BEGINDATA
1 10
20lg

如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format

==========================================================================================================
SQLLoader 使用指南（转载）
SQLLoader是Oracle数据库导入外部数据的一个工具.它和的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载.
如何使用 SQLLoader 工具
我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件（含分隔符). loader.ctl如下:
load data
infile ‘c:\data\mydata.csv’
into table emp
fields terminated by “,” optionally enclosed by ‘"’
( empno, empname, sal, deptno )
mydata.csv 如下:
10001,“Scott Tiger”, 1000, 40
10002,“Frank Naude”, 500, 20
下面是一个指定记录长度的示例控制文件。"" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQLPlus的select 及 format 数据来输出到一个文件：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ‘,’ || col2 || ‘,’ || col3
from tab1
where col2 = ‘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’,‘tab1.txt’,‘w’);
utl_file.putf(fp, ‘%s, %s\n’, ‘TextField’, 55);
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
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,“A,B,C,D,”
下面是导入固定位置（固定长度）数据示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳过数据行：
可以用 “SKIP n” 关键字来指定导入时可以跳过多少行数据。如：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
导入数据时修改数据：
在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no “my_db_sequence.nextval”,
region CONSTANT ‘31’,
time_loaded “to_char(SYSDATE, ‘HH24:MI’)”,
data1 POSITION(1:5) “:data1/100”,
data2 POSITION(6:15) “upper(:data2)”,
data3 POSITION(16:22)“to_date(:data3, ‘YYMMDD’)”
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
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(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ’ ’
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
导入选定的记录：
如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
LOAD DATA
INFILE ‘mydata.dat’ BADFILE ‘mydata.bad’ DISCARDFILE ‘mydata.dis’
APPEND
INTO TABLE my_selective_table
WHEN (01) <> ‘H’ and (01) <> ‘T’ and (30:37) = ‘19991217’
(
region CONSTANT ‘31’,
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
导入时跳过某些字段：
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ‘,’
( field1,
field2 FILLER,
field3
)
导入多行记录：
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE: - use when SQLLoader 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 1.
SQLLoader 数据的提交：
一般情况下是在导入数据文件数据后提交的。
也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQLLoader 的性能：

  1. 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
  2. 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
  3. 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
  4. 可以同时运行多个导入任务.
    常规导入与direct导入方式的区别：
    常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

补充贴出我得导入过程：

1.编辑ctl控制文件。
1.1指定已加密数据文件csv的路径
1.2指定数据库表中列的对应关系
eg: yourtablename.ctl

    LOAD DATA

   INFILE 'K:\1.csv'
   INFILE 'K:\2.csv'
   INFILE 'K:\3.csv'
   INFILE 'K:\4.csv'
   INFILE 'K:\5.csv'
   INFILE 'K:\6.csv'

   APPEND INTO TABLE new123
   FIELDS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
   (ND,F_YLZH,F_SNYLZH,F_SNYE,F_BNSR,F_BNZC,F_BNJY)

2.在cmd命令行中执行sqlldr命令。
2.1指定ctl文件、log文件、直接方式 direct/parallel

sqlldr userid=user/mima control='K:\导入数据\ yourtablename.ctl' log='K:\导入数据\yourtablename.log' skip=1 direct=true parallel=true streamsize=1024000
oracle sqlload速度加快,Oraclesqlldr导入数据使用默认值和提高性能的方法
weixin_29743937的博客
04-02 1322
在实际中常常有大量的数据需要导入到目前的数据库表中，oracle提供的sqlldr命令很好的meet了这个需求。在使用的过程中，我们发现在实际中常常有大量的数据需要导入到目前的数据库表中，，Oracle提供的sqlldr命令很好的meet了这个需求。在使用的过程中，我们发现，如果在数据库的table端的Column中设置default值，但是使用sqlldr就不会起作用，也就是默认值没有设置成功，...
OracleERP云数据迁移与同步技术教程
kkchenjj的博客
08-21 794
在Oracle ERP Cloud的二次开发中，构建自定义接口是实现数据迁移与同步的关键步骤。这一过程涉及到理解ERP系统的数据模型，以及如何通过APIs与外部系统进行有效通信。数据准备：在迁移前，需要对源数据进行彻底的清理和验证，确保数据的准确性和一致性。这通常涉及数据清洗、去重和格式化。迁移工具选择：Oracle提供了多种工具来支持数据迁移，包括Data Loader、Data Migration Workbench和GoldenGate等。选择合适的工具取决于数据量、数据复杂度和迁移频率。数据映射。
oracle sqlloader 数据快速导入
zhaohp的博客
07-11 498
sqlldr username/password control=/home/oracle/sqlldr/mdm.ctl log=/home/oracle/sqlldr/mdm.log bad=/home/oracle/sqlldr/mdm.bad data=/home/oracle/sqlldr/mdm.txt rows=1000 sqlloader 控制文件 sqlload.ctl LOAD DATA INFILE 'd:\data.txt' INTO TABLE test TR
利用sqlldr大量数据导入
12-02
利用sqlldr大量数据导入，方便，快速
mysql有sqlldr命令吗
最新发布
weixin_40394754的博客
08-29 49
我整理的一些关于【MySQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkR使用 MySQL 数据库与 SQL*Loader 相似的工具实现数据导入 在这篇文章中，我将引导你理解如何在 MySQL 中实现类似于 Oracle SQL*Loader 的数据加载功能...
ORACLE SQLLOADER入库效率测评1800W/h
Daemon
08-24 1359
SQLLOADER：文本文件 近70个字段 大小30G 数据条数约7300W 用时约4小时 效率大概为1800W/h 半小时效率大概在930W左右
oracle sqlload速度加快,Oracle sqlldr导入数据使用默认值和提高性能的方法
weixin_42499332的博客
04-02 730
在实际中常常有大量的数据需要导入到目前的数据库表中，Oracle提供的sqlldr命令很好的meet了这个需求。在使用的过程中，我们发现，如果在数据库的table端的Column中设置default值，但是使用sqlldr就不会起作用，也就是默认值没有设置成功，所以需要设置默认值得时候，我们将默认值写到了ctl文件中。ctl文件如下：Load datainfile 'd:\temp\buser.t...
使用sqlldr导入数据的一点问题
bantaoti1130的博客
06-09 431
1.控制文件(add_test.ctl): LOAD DATA INFILE sqlexport_ALLutf_8.txt APPEND INTO TABLE hbkf_crm.ogu_baseinfo_list_201305wd ...
大数据分析的案例、方法与挑战.docx
05-20
2. **入库方式**：使用SQL*Loader进行数据导入，但在数据量过大时遇到了瓶颈。研究团队通过启用多个处理机来尝试解决问题，但发现这种方式会导致多个入库节点之间的同步问题。 3. **性能监控**：通过Oracle的AWR...
Oracle 12c中的数据仓库设计与实施
数据仓库是指把企业各个部门的数据集成到一起，用于支持管理决策的过程性存储。它是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。 在数据仓库中，数据是按时间进行分组的，通常...
Oracle字符集的设置
C语言技术网-码农有道
03-25 1814
文章目录一、字符集的概念1、字符编码（character encoding）：2、字符集（Character set）3、国际编码（Unicode）4、汉字的编码5、汉字的编码选择6、编码的转换二、Oracle的字符集1、字符集和国家字符集2、支持中文的字符集3、NLS_LANG参数三、服务端的字符集1、查看服务端字符集2、修改服务端字符集四、客户端的字符集1、Linux环境2、Windows环境...
oracle sqlldr数据导入方法,oracle之sqlldr命令数据导入
weixin_35653388的博客
04-03 2543
创建控制文件,以ctl为后缀名(test.ctl),内容如下；load datainfile test.txtappend into table test_tablefields terminated by ","(id,name,age)其中:test.txt为需要导入到数据库的文件;test_table为需要导入数据的数据库表;","为导入文件的分隔符.导入数据分成四种模式,可以根据需求选择:...
SQL_Loader 固定格式加载数据
ggz 的博客
08-09 251
1、创建需要加载数据的表 create table student( stuno number(4), stuname varchar2(20), sex varchar2(4), old number(4) ); 2、制作一份文本数据 vi student.txt 1001,东方,男,30 1002,开心,女,25 1003,jack,男,23 1004,rose,女,20 3、编辑控制文件 vi student.ctl load data infile '/home/oracle/or
使用sqlloader导入数据（千万级）-oracle
weixin_42174839的博客
03-29 4892
表结构相同的新旧表之间的数据迁移
sqlldr/sqlload导入数据时数据量不一致的问题
s9434的博客
11-20 4182
sqlldr/sqlload导入数据时数据量不一致的问题 目前遇到两种情况。 一、ctl文件中的字段后面没有跟数据类型。 如果某个文件中，该字段下的内容长度大于256而你的ctl文件中的字段后面没有加数据类型和长度，则会报错，无法导入。 如，main_product 主营产品字段，如果该字段的内容超过256，而你ctl文件中写的只有 main_product 则会报错。需要改为main_pro...
sqlldr导入报错：field in data file exceeds maximum length
热门推荐
killvoon的专栏
10-23 1万+
客户用sqlldr导入数据时候报错： " Record 1: Rejected - Error on table ks_test, column khname. Field in data file exceeds maximum length " 看到这个错误后以为是字段值过小导致，将原来的varchar2(1000) 改到了varchar2(2000) 发现还是不可以，依旧报如上错
使用sqlldr导入数据时导入部分就停止了
驽马十驾 才定不舍
03-20 2871
iamlaosong文在使用sqlldr导入数据到Oracle时发现数据短少的厉害，检查日志发现有部分数据实际长度大于字段长度无法导入（存放在bad文件中），但这部分数据量并不大，实际数据有几十万条，但只导入2999条就停了，此时异常数据达到51条。增加字段长度重新导入，一切正常。看来这个异常数据条数是有限制的，超过了后面的数据就不再导入了。Oracle修改字段类型和长度语句：ALTER TABL...
使用SQLLOADER将多行数据合并为一行进行加载
09-17 233
SQL*LOADER支持将多行数据（物理行）合并为一行（逻辑行）处理，本文进行测试验证。 1，语法说明：1.1，使用CONCATENATE合并固定行数为一行format:CONCATENATE integer 1.2，使...
Oracle SQL*Loader使用教程：快速批量导入数据
"了解和使用SqlLoader来高效地批量导入数据到Oracle数据库是数据库管理中的重要技能。SqlLoader，也称为SQL*Loader，是Oracle提供的一种快速数据加载工具，能够处理各种平面文件格式的数据，适用于大量数据的导入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值