【12c】Oracle 12c SQL*Loader介绍
SQL*Loader是Oracle提供的一个非常有用的工具，可以实现批量数据的高速加载，它将数据从外部文件加载到Oracle数据库的表中，本篇将对Oracle 12c的SQL*Loader进行简单的介绍和演示。
1 SQL*Loader介绍
SQL*Loader的使用，是通过Oracle的命令行指令sqlldr实现，该指令是客户端工具，可通过KEY-VALUE的形式指定对应的选项，或者使用参数文件和控制文件来实现，下面展示的是SQL*Loader的工作原理：
控制文件主要用于控制数据加载的行为，包括在哪里找到数据，如何解析和解释数据，以将数据插入到哪里等等，通常来说，控制文件包括三部分内容：
- 会话范围的信息；
- 表和字段列表信息；
- 输入数据；
对于数据的加载，SQL*Loader提供了三种方法分别是：
- 传统路径加载；
- 直接路径加载；
- 外部表加载；
2 SQL*Loader指令语法
SQL*Loader通过指令sqlldr实现，输入该命令回车，即可看到该指令对应的选项信息：
[oracle@odd ~]$ sqlldr
SQL*Loader: Release 12.1.0.2.0 - Production on Fri May 8 20:08:37 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
partition_memory -- direct path partition memory limit to start spilling (kb) (Default 0)
table -- Table for express mode load
date_format -- Date format for express mode load
timestamp_format -- Timestamp format for express mode load
terminated_by -- terminated by character for express mode load
enclosed_by -- enclosed by character for express mode load
optionally_enclosed_by -- optionally enclosed by character for express mode load
characterset -- characterset for express mode load
degree_of_parallelism -- degree of parallelism for express mode load and external table load
trim -- trim type for express mode load and external table load
csv -- csv format data files for express mode load
nullif -- table level nullif clause for express mode load
field_names -- field names setting for first record of data files for express mode load
dnfs_enable -- option for enabling or disabling Direct NFS (dNFS) for input data files (Default FALSE)
dnfs_readbuffers -- the number of Direct NFS (dNFS) read buffers (Default 4)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.3 SQL*Loader示例
3.1 示例一
加载数据文件，数据文件存放于控制文件中。
1）创建测试表
SQL> create table dept(deptno number(2),dname varchar2(14),loc varchar2(13));
Table created.2）创建控制文件
[oracle@odd loader]$ cat test01.ctl
load data --告诉SQL*Loader将执行数据加载操作
infile * --指定要加载的数据文件，若文件在控制文件中，需指定为*
into table dept --指定要将数据加载到的表
fields terminated by ',' optionally enclosed by '"' --指定字段分割字符
(deptno,dname,loc) --指定要加载到表里的字段名称
begindata --表示下面是要加载的数据，即数据在控制文件里时，需要使用begindata
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"说明：
into向表里加载数据有四种选项，分别为：insert、append、replace和truncate。
file可以指定infile、badfile、discardfile。
如果使用直接路径加载，需指定选项DIRECT=TRUE。
3）加载数据
[oracle@odd loader]$ sqlldr alen/alen@odd control=test01.ctl log=test01.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon May 11 20:12:57 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 7
Table DEPT:
7 Rows successfully loaded.
Check the log file:
test01.log
for more information about the load.4）查看数据
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
12 RESEARCH SARATOGA
10 ACCOUNTING CLEVELAND
11 ART SALEM
13 FINANCE BOSTON
21 SALES PHILA.
22 SALES ROCHESTER
42 INT'L SAN FRAN
7 rows selected.3.2 示例二
如果数据在数据文件里，则在控制文件不需要指定数据。
1）创建数据文件
[oracle@odd loader]$ cat test02.dat
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"2）创建控制文件
[oracle@odd loader]$ cat test02.ctl
load data
infile 'test02.dat' --指定数据文件，可以时单独的文件名，也可以带有目录的文件名
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno,dname,loc)3）加载数据
[oracle@odd loader]$ sqlldr alen/alen@odd control=test02.ctl log=test02.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon May 11 20:23:28 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 7
Table DEPT:
7 Rows successfully loaded.
Check the log file:
test02.log
for more information about the load.3.3 示例三
如果数据文件中，有列头，那么进行数据加载时，可能会出错，生成一个bad文件，如果跳过，可以指定SKIP。
1）创建数据文件
[oracle@odd loader]$ cat test03.dat
deptno,dname,loc
12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"2）创建控制文件
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore --指定该配置，可忽略字段头信息
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno,dname,loc)3）数据加载
[oracle@odd loader]$ sqlldr alen/alen@odd control=test03.ctl log=test03.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon May 11 20:33:07 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 7
Table DEPT:
7 Rows successfully loaded.
Check the log file:
test03.log
for more information about the load.3.4 示例四
对于数据列，可以指定字段的数据类型。
1）创建控制文件，指定字段类型
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno integer external, --指定字段类型
dname char, --指定字段类型
loc char) --指定字段类型2）创建控制文件，指定常量值
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno integer external,
dname char,
loc char,
ext1 constant 'alen') --指定常量值3）创建控制文件，指定当前时间
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
(deptno integer external,
dname char,
loc char,
ext1 sysdate) --指定当前时间4）创建控制文件，指定使用序列
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
date format 'yyyy-mm-dd'
(deptno integer external,
dname char,
loc char,
ext1 sequence (1,1)) --指定序列序列参数：
5）创建控制文件，使用函数加载数据
[oracle@odd loader]$ cat test03.ctl
load data
field names all files ignore
infile 'test03.dat'
into table dept
fields terminated by ',' optionally enclosed by '"'
date format 'yyyy-mm-dd'
(deptno integer external,
dname char,
loc char "lower(:loc)", --将该字段转为小写
ext1 sequence (1,1))3.5 示例五
指定选项EXTERNAL_TABLE=GENERATE_ONLY，可以生成外部表。
[oracle@odd loader]$ sqlldr alen/alen@odd control=test03.ctl external_table=generate_only log=test03.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon May 11 22:16:43 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: External Table
[oracle@odd loader]$ cat test03.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon May 11 22:16:43 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: test03.ctl
Data File: test03.dat
Bad File: test03.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , O(") CHARACTER
DNAME NEXT * , O(") CHARACTER
LOC NEXT * , O(") CHARACTER
SQL string for column : "lower(:loc)"
EXT1 SEQUENCE (1, 1)
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/loader'
sequences created to simulate SEQ in control file:
------------------------------------------------------------------------
CREATE SEQUENCE SYS_SQLLDR_X_SEQ_000 MINVALUE 1 START WITH 1 INCREMENT BY 1
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test03.bad'
LOGFILE 'test03.log_xt'
READSIZE 1048576
FIELD NAMES ALL FILES IGNORE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
DATE_FORMAT DATE MASK 'yyyy-mm-dd'
REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"DNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LOC" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test03.dat'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT
(
DEPTNO,
DNAME,
LOC,
EXT1
)
SELECT
"DEPTNO",
"DNAME",
lower("LOC"),
SYS_SQLLDR_X_SEQ_000.nextval
FROM "SYS_SQLLDR_X_EXT_DEPT"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP SEQUENCE SYS_SQLLDR_X_SEQ_000
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Mon May 11 22:16:43 2020
Run ended on Mon May 11 22:16:44 2020
Elapsed time was: 00:00:00.22
CPU time was: 00:00:00.01
