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轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2488.html
標籤：Oracle