ORACLE->SQL*Loader[20180712]
SQL*Loader将外部文件的数据加载到Oracle数据库的表中。它具有强大的数据解析引擎，对数据文件中的数据格式没有太多的限制。
SQL*Loader可执行以下操作：
-如果数据文件与数据库不在同一系统上，则通过网络加载数据。
-在同一会话中可以加载来自多个数据文件的数据。
-在同一加载会话期间可将数据文件的时间加载到多个表中。
-可指定数据的字符集
-选择性加载数据
-使用SQL函数在加载数据之前操作数据
-在指定的列中生成唯一的序列值
-使用操作系统的文件系统访问数据文件
-从磁盘，磁带或命名管道加载数据
-生成错误报告帮助解决问题
-加载任意复杂的对象关系数据
-使用辅助数据文件来加载LOB和集合
-使用传统或直接路径加载。虽然传统的路径加载非常灵活，但直接路径加载提供了出色的性能
SQL*Loader 参数
sqlldr命令，可以调用SQL*Loader
参数可以和参数文件中组合在一起。然后使用parfile参数指定参数文件的名称
某些参数也可以通过使用options子句在SQL*Loader控制文件中指定
在命令行中指定的参数将覆盖参数文件或options子句中指定的任何参数值
输入数据文件
SQL*Loader从控制文件中指定的一个或多个文件（或操作系统的等效文件）中读取数据。从SQL*Loader的角度看，数据文件中的数据是按记录组织的。一个特定的数据文件可采用固定记录格式、可变记录格式或流记录格式。可通过控制文件中INFILE参数指定记录格式。如果未指定记录格式，默认格式为流记录格式。
SQL*Loader控制文件
控制文件是一个文本文件，它是使用SQL*Loader可识别的语言编写的。控制文件指示SQL*Loader在何处查找数据、如果分析和解释数据以及何处插入数据等等。
控制文件包含三个段
第一个段包含以下所示的会话范围信息
-全局选项，如输入数据文件名和要跳过的记录
-用于指定输入数据位置的INFILE子句
-要加载的数据
第二个段包括一个或多个INTO TABLE块。其中每个一块都包含要字在其中加载数据的表的信息（如表名和表列）
第三个段是可选段，如果存在，则其中包含输入数据
SQL*Loader日志文件
SQL*Loader开始执行时，会创建日志文件。如果不能创建日志文件，执行就会终止。日志文件包含加载操作的详细说明，包含加载过程中发生的任何错误的说明。
SQL*Loader 坏文件
坏文件中包含被SQL*Loader或Oracle DB拒绝的记录。当输入格式无效时，SQL*Loader就会拒绝数据文件记录。SQL*Loader接受处理某一数据文件记录后，会将该数据文件发送到Oracle DB。以便能够作为一行插入到表中。如果Oracle DB确定该行有效，就会将该行插入到表中；如果确定该行无效，则会拒绝该记录，然后SQL*Loader会将该记录放入坏文件中。
SQL*Loader放弃文件
仅当需要这种文件并且指定了启用放弃文件时，才会创建此文件。放弃文件中包含的记录是因不符合控制文件指定的任何记录选择标准而从加载文件中过滤掉的记录。
SQL*Loader控制文件通知SQL*Loader以下信息
要加载数据的位置
数据格式
配置详细资料
-内存管理
-记录拒绝
-中断的加载处理详细资料
数据操作详细资料
SQL*Loader控制文件是一个文本文件,其中包含数据定义语言(DDL)指令.DDL用来控制SQL*Loader会话的以下方面:
-SQL*Loader在何处查找要加载的数据
-SQL*Loader希望如何确定数据的格式
-SQL*Loader在加载数据时采用了那些配置(包括内存管理,选择与拒绝标准,中断的加载处理等等)
-SQL*Loader如何处理正在加载的数据
SQL*Loader加载方法
直接路径加载与常规路径加载的比较
常规路径加载通过执行SQL INSERT语句,将表填充到ORACLE DB中.直接路径加载通过格式化oracle数据块并将其直接写入数据库文件，消除了大部分Oracle DB开销。直接加载不与其他用户争用数据库资源，因此其数据加载速度通常与磁盘速度相差无几。常规路径加载使用SQL处理和数据库COMMIT操作来保存数据。插入记录数组后要执行COMMIT操作。每次数据加载可能涉及多个事务处理。
直接路径加载使用数据保存将数据块写入Oracle数据文件。这就是为什么直接路径加载比常规路径加载快很多的原因。通过以下特性可区分数据保存与COMMIT的差异：
-在数据保存期间，只有完整的数据块才写入数据库中
-这些块是在按照表的高水位标记（HWM）写入的
-完成数据保存后，HWM会移动
-完成数据保存后不会释放内部资源
-完成数据保存后不会结束事务处理
-每次执行数据保存时不会更新索引
SQL*Loader测试
加载数据和数据文件
Fixed Record Format 固定长度 INFILE datafile_name "fix n" 固定11个字节 [oracle@pcqtestxi01 ~]$ cat example.ctl example.dat ----example.ctl ---- load data infile 'example.dat' "fix 11" into table example fields terminated by ',' optionally enclosed by '"' (col01,col02) ----example.dat ---- 001, cd, 0002,fghi, 00003,lmn, 1, "pqrs", 0005,uxwx, [oracle@pcqtestxi01 ~]$ sqlldr monitor/password control=example.ctl SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jun 12 11:45:16 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 5 查询加载成功的数据 SQL> select * from dbmonitor.example; COL01 COL02 ----- -------------------------------------------------- 001 cd 0002 fghi 00003 lmn 1 pqrs 0005 uxwx Variable Record Format 变量记录格式 INFILE datafile_name "var n" [oracle@pcqtestxi01 ~]$ cat example01.ctl example01.dat ----example01.ctl------ load data infile 'example01.dat' "var 3" into table example fields terminated by ',' optionally enclosed by '"' (col01 char(5), col02 char(7)) ----example01.dat------ 009hello,cd,010world,im, 012my,name is, [oracle@pcqtestxi01 ~]$ sqlldr monitor/password control=example01.ctl SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jun 12 11:41:03 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 3 [oracle@pcqtestxi01 ~]$ dba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 12 11:41:06 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from dbmonitor.example; COL01 COL02 ----- -------------------------------------------------- hello cd world im my name is Stream Record Format 流记录格式 INFILE datafile_name ["str terminator_string"] terminator_string被指定为'char_string'或X'hex_string'，其中： 'char_string'是用单引号或双引号括起来的字符串 X'hex_string'是一个十六进制格式的字节串 当terminator_string包含特殊（不可打印）字符时，应该将其指定X'hex_string。但是通过使用反斜杠，可以将一些不可打印的字符指定为（‘char_string’） \ n表示换行 \ t表示一个水平标签 \ f表示换页 \ v表示一个垂直选项卡 \ r表示回车
SQL*Loader 命令行参数
CONTROL 指定控制文件名称
OPTIONS 条件选项 OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK) )
PARAMETER 参数文件
命令行参数
BAD 指定坏文件名称
BINDSIZE 指定BIND数组最大的字节，它的默认值 依赖操作系统和ROWS数
COLUMNARRAYROWS 指定为直接路径分配的行数，必须指定它或接受默认值
DATA 数据文件，在控制文件INFILE中指定，如果命令行中指定了数据文件，则会忽略控制文件中的第一个数据文件
DATE_CACHE 日期缓存，用于存储从文本字符串到内部日期格式的转换结果。如果数据文件中重复出现相同的日志，则使用日期缓存可以提高直接路径加载的速度。默认值1000，只适用于直接路径加载
DIRECT 直接路径加载，默认值false 不启用，true 启用
DISCARD 丢弃文件，指用于存储既不插入表也不被拒接的记录
DISCARDMAX 指定在数据加载终止之前允许的丢弃记录数。默认值ALL
ERRORS 指定允许的最大插入错误数量。如果错误数量超过了ERRORS指定的值，那么SQL*Loader终止加载
EXTERNAL_TABLE 指示SQL*Loader是否使用外部表选项加载数据
NOT_USED 默认值。使用常规或直接路径模式执行
GENERATE_ONLY 在SQL*Loader日志文件中放置使用外部表进行加载所需的所有SQL语句，不会真实执行。
EXECUTE 尝试执行使用外部表执行装入所需的SQL语句
FILE 要加载到的表空间文件。它仅用于直接路径并行加载
LOAD 指定要加载的逻辑记录的最大数量（跳过指定数量之后的记录）
LOG 指定SQL*Loader将创建的日志文件，存储加载过程的日志记录
MULTITHREADING 多线程功能，多CPU系统上为true，单CPU系统上为false。该参数仅适用于直接路径加载
PARALLEL 并行度
PARFILE 指定包含常用命令行参数的文件名称
READSIZE 读取缓冲区大小，较大的读取缓冲区的优点是在需要提交操作之前可以读取更多的数据
RESUMABLE 用于启用和禁用可恢复的空间分配，默认值false
RESUMABLE_NAME 此参数的值标识可恢复的语句。此值会插入到USER_RESUMABLE或DBA_RESUMABLE视图中，用于帮助识别已暂停的特定可恢复语句。除非RESUMNABLE为true，否则将忽略此参数
RESUMABLE_TIMEOUT 指定必须修正错误的时间段。如果错误在超时期限内没有解决，语句执行就会终止，而不会结束。除非RESUMNABLE为true，否则将忽略此参数。默认值 7200秒，2小时
ROWS 每次提交的行数。
仅限传统路径加载：ROWS指定数组中的行数
仅限直接路径加载：ROWS标识在数据保存之前要从数据文件读取的行数。
SILENT 反馈消息， SILENT=(HEADER, FEEDBACK)
HEADER 抑制SQL*Loader标头消息
FEEDBACK 禁止已提交的反馈信息
ERRORS 禁止数据错误信息
DISCARDS 禁止丢失信息
PARTITIONS 直接加载分区表期间禁用将每分区统计信息写入日志文件
ALL 抑制所有值，HEADER、FEEDBACK、ERRORS、DISCARDS和PRATITIONS
SKIP 记录跳过，该参数继续由于某种原因而中断的加载
SKIP_INDEX_MAINTENANCE 默认值false，停止直接路径加载对索引维护，但不适用用于常规路径加载。
适用于本地和全局索引
可以使用PARALLEL参数，对具有索引对象执行并行加载
可以使用INTO TABLE子句中的PARTITION参数，对具有全局索引的表执行单个分区加载
将加载设置为索引不可用状态的索引和索引分区的列表
SKIP_UNUSABLE_INDEXES 默认值true，如果遇到索引不可用状态下的索引，则会跳过该索引并继续加载操作
STREAMSIZE 指定直接路径流的大小（以字节为单位）
USERID 用于提供Oracle用户名和密码，如果省略则会提示输入，如果以SYS身份连接，则必须指定AS SYSDBA
退出时检查和显示代码，不同平台提示的数值代码
|
Result
|
Exit Code
|
|
All rows loaded successfully
|
EX_SUCC
|
|
All or some rows rejected
|
EX_WARN
|
|
All or some rows discarded
|
EX_WARN
|
|
Discontinued load
|
EX_WARN
|
|
Command-line or syntax errors
|
EX_FAIL
|
|
Oracle errors nonrecoverable for SQL*Loader
|
EX_FAIL
|
|
Operating system errors (such as file open/close and malloc)
|
EX_FAIL
|
For UNIX, the exit codes are as follows:
EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 3
For Windows NT, the exit codes are as follows:
EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 4
内容领域
控制数据的领域，position(1:2)截取字符1到2位置，作为一个字段
.
.
.
1 (hiredate SYSDATE,
2 deptno POSITION(1:2) INTEGER EXTERNAL(2) NULLIF deptno=BLANKS,
3 job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')",
4 comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%' ":comm * 100" )
SQL*Loader Field List Reference
（用于指定数据截取、类型和限制）
1.Field List Contents
(数据内容格式)
(字段名 截取范围 类型|类型)
POSITION(start:end) 指定截取字符范围
INTEGER EXTERNAL(类型,整型)
NULLIF(空时赋初始值)
TERMINATED BY WHITESPACE(分隔符)
ENCLOSED BY(结束符)
2.Specifying the position of a data filed
(指定数据所在范围)
语法:(start|*/+integer :或- end)
start 开始位置
end 结束位置
* 从前面位置以后的
+integer 指定取多少值
3.Specifying columns and fields
(指定字段和列)
Specifying filler fields(填充字段)
BOUNDFILLER或FILLER指定填充字段
规则
填充字段的语法与基于列的字段语法相同，只是填充段的名称后跟上填充字符
填充字段具有名称，但它们未加载到表中
填充字段可用作init_spec的参数（如NULL和DEFAULTIF）
填充字段可用作指令的参数（如SID、OID、REF和BFILE）
注意事项
填充字段可用于NULLIF、DEFAULT和WHEN子句中的字段条件规范。但是它们不能在SQL字符串中使用
填充字符规范不能包含NULLIF或DEFAULT子句
如果指定并适用TRAILING NULLCOLS,则填充字段将初始化为NULL.如果另一个字段引用了无效的填充字段，则会生成错误
填充字段可以出现在数据文件中的任何位置，包含对象的字段列表内或VARRAY的定义内
SQL字符串不能指定为填充字段规范的一部分，因为没有绑定数组中的填充程序分配空间
4.SQL*Loader datatypes
(数据类型)
Nonportable Datatypes
(不可移植数据类型)
数值类型
INTEGER(n:1,2,4,8) 全二进制整数
SMALLINT 半二进制整数
FLOAT 单精度浮点二进制数
DOUBLE 双精度浮点二进制数
BYTEINT 二进制表的十进制值，BYTEINT长度始终为1
ZONED 分区十进制数
DECIMAL 压缩十进制数
长数值类型
VARGRAPHIC 变长的双字节字符集
VARCHAR 字符串
VARRAW 一个2字节的二进制和一个RAW字符串组成
LONG VARRAW 具有4个字节长度的子字段(VARRAW)
Portable Datatypes
(可移植数据类型)
数值类型
CHAR (CHAR length delim_spec)
Datetime and Interval
DATE
TIME
TIME WITH TIME ZONE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
GRAPHIC 双字节字符集 graphic graphic_char_length
GRAPHIC EXTERNAL 双字节字符集
Numeric EXTERNAL(INTEGER,FLOAT,DECIMAL,ZONED)
RAW 原二进制数据
长数据类型
VARCHARC 字符长度子字段和字符串值子字段组成
VARRAWC 由RAW字符串子字段组成
|
Table 9-2 Datatype Conversions for Datetime and Interval Datatypes
|
|
|
SQL*Loader Datatype
|
Oracle Database Datatype (Conversion Support)
|
|
N
|
N (Yes), C (Yes), D (No), T (No), TS (No), YM (No), DS (No)
|
|
C
|
N (Yes), C (Yes), D (Yes), T (Yes), TS (Yes), YM (Yes), DS (Yes)
|
|
D
|
N (No), C (Yes), D (Yes), T (No), TS (Yes), YM (No), DS (No)
|
|
T
|
N (No), C (Yes), D (No), T (Yes), TS (Yes), YM (No), DS (No)
|
|
TS
|
N (No), C (Yes), D (Yes), T (Yes), TS (Yes), YM (No), DS (No)
|
|
YM
|
N (No), C (Yes), D (No), T (No), TS (No), YM (Yes), DS (No)
|
|
DS
|
N (No), C (Yes), D (No), T (No), TS (No), YM (No), DS (Yes)
|
