传统路径：sqlldr会利用sql插入为我们加载数据
直接路径加载：sqlldr不适用sql，直接格式化数据块，绕开undo，避开redo，最快的方法就是并行直接路径加载
sqlldr只是一个命令行工具，并非一个api，在plsql中不能调用
LOAD DATA—告诉sqlldr做什么，可以用
INFILE *
INTO TABLE BONUS
Insert-----默认
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523
--
LOAD DATA—告诉sqlldr做什么，可以用CONTINUE_LOAD来继续加载
infile *——表示数据文件的位置，为*表示数据文件在控制文件ctl中。如果是一个路径，表示数据与控制文件分离的。
into table bonus——表示插入表bonus，该表在sqlldr命令执行前就已经创建好。
into还有些参数：insert ：向表中插入数据，此表必须为空，默认的参数insert
append：向表中追加数据，不管表中是否有数据
replace：替换表中数据，相当于先delete在insert
truncate：先truncate表中数据，在insert
Fields terminated by ‘，’表示数据部分的分隔符是逗号，，也可以替换成其他任何可见字符
(ENAME,JOB,SAL) 要插入表的列名
Bingdata 表示以下为加载的数据，当infile 为*有效
OPTIONALLY ENCLOSED BY 指明定界符
sqlldr在默认情况下，会在sqlldr执行过程中，产生一个与控制文件同名的日志文件，。Log，日志文件中记录了加载数据的各项统计信息，
错误文件，在加载过程中，由于数据不符合规范就会生成一个与控制文件同名的错误文件，
废弃文件，。Dsc默认不会有
sqlldr userid=/ control=demo1.ctl
sqlldr userid/@orcl control=demo1.ctl
将excel文件另存为csv格式的文件
然后控制文件中
LOAD DATA
INFILE 'F:sqlldr1024TEST.csv' --指定文件名
BADFILE 'F:sqlldr1024TEST.bad'
1 可以修改数据文件，将其他分隔符替换为逗号
2 修改控制文件，FIELDS TERMINATED BY ","，","修改为实际的符号
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,"Virginia,USA"---"Virginia,USA" 及一个字段
结果就是 10 Sales Virginia,USA
20 Accounting Va, "USA"
OPTIONALLY ENCLOSED BY 指明定界符
数据文件中没有分隔符，在控制文件中
(
ENAME position(1:5),
JOB position(7:15),
SAL position(17:20)
) 用position关键字来指定列的起始结束位置，比如JOB position(7:15)，job从第7个字符开始到第15个字符截止，
position可以position(*+2:15),用*相对偏移量，上次从哪里结束，下个字段就从哪里开始
postion(*)char(9)
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ---------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
表dept有3个列
控制文件
LOAD DATA
INFILE ldr_case3.dat
TRUNCATE INTO TABLE dept
(
ENAME position(1:5),
JOB position(7:15),
SAL "0") 在控制文件中增加一列
或者
SAL “substr(:job,1,1)”
在控制文件中可以用FILLER指定过滤列。
(
ENAME position(1:6),
TCOL FILLER position(8:11),
JOB position(13:21),
SAL position(23:26)）
如果数据文件不是定长格式，而是通过分隔符来处理的，
FIELDS TERMINATED BY ","
(ENAME,TCOL FILLER,JOB,SAL) 过滤了数据文件中的第二列？
LOAD DATA
INFILE ldr_case8_1.dat
INFILE ldr_case8_2.dat
INFILE ldr_case8_3.dat
LOAD DATA
INFILE ldr_case9.dat
DISCARDFILE ldr_case9.dsc
TRUNCATE
INTO TABLE BONUS
WHEN TAB='BON'
(TAB FILLER POSITION(1:3),
ENAME POSITION(5:9) ,
JOB POSITION(*+1:18),
SAL POSITION(*+1)
)
INTO TABLE MANAGER
WHEN TAB = 'MGR'
(TAB FILLER POSITION(1:3),
MGRNO POSITION(4:5) ,
MNAME POSITION(7:13),
JOB POSITION(*+1))
指定了when关键字，when逻辑判断不知道or，连接条件只能有and，不支持or
When字句不是使用区间大于或小于，没有or，没有is null等
sqlldr userid/@orcl control=demo1.ctl skip=N
sqlldr userid/@orcl control=demo1.ctl skip=4 LOAD=6及导入4到9行
Windows下回车+换行 chr(13)+chr(10),linux chr(10)
1 手工指定换行符
LOAD DATA
INFILE ldr_case11_1.dat
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ","
(MGRNO,
MNAME,
JOB,
REMARK "replace(:remark,'\n',chr(10))"
)
2 指定FIX属性来处理换行符（定长数据专用）
10,SMITH,SALES MANAGER,This is SMITH. He is a Sales Manager.
Ctl
LOAD DATA
INFILE ldr_case11_2.dat "fix 68"—包含换行在内的68个字符
TRUNCATE INTO TABLE MANAGER
(
MGRNO position(1:2),
MNAME position(*+1:10),
JOB position(*+1:24),
REMARK position(*+1:65)
)
3 用var来处理换行
LOAD DATA
INFILE ldr_case11_3.dat "var 3" 通过var属性来指定每行开头固定的字符串长度
4 指定str属性来处理换行
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
INFILE ldr_case11_4.dat "str '| '"
Windows 中 select utl_raw.cast_to_raw( '|'||chr(10) ) from dual;
Ctl
INFILE demo.dat "str X'7C0A'"
1 数据文件保存在控制文件中
先修改表的列类型为clob
(MGRNO, MNAME, JOB, REMARK char()) 指定列的长度，
2 数据文件保存在独立的文件中
create table lob_demo
2 ( owner varchar2(255),
3 time_stamp date,
4 filename varchar2(255),
5 data blob
6 )
Ctl
LOAD DATA
INFILE *
REPLACE
INTO TABLE LOB_DEMO
( owner position(17:25),
time_stamp position(44:55) date "Mon DD HH24:MI",
filename position(57:100),
data LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
-rw-r--r-- 1 tkyte tkyte Jun 17 15:26 classes12.zip
FIELDS TERMINATED BY "," TRAILING NULLCOLS
当某行对应的列没有值时，sqlldr自动赋值为null，而不是报错
lOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME,
LOC,
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
LAST_UPDATED date 'yyyy-mm-dd hh24:mi:ss'
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)", ---loc “222”该列所有值都替换成222
LAST_UPDATED
"case
when length(:last_updated) > 9
then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')
when instr(:last_updated,':') > 0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'dd/mm/yyyy')
end"
append
INTO TABLE BULK_NUMBERS
FIELDS TERMINATED BY ','
Optionally enclosed by '"'
trailing nullcols
(id ,
a "substr(upper(:a),1,2)" ,--- a "replace(:a,:a,'000')",字符串类型
b "replace(:b,:b,)",
c ,
DATE1 date "MM-DD-YYYY HH24:MI:SS")
----------------b "replace(:b,:b,)"
-----------------b "222" a " '000222' ",单引号跟双引号之间有空格
LOAD DATA
INFILE Book1.csv
APPEND INTO TABLE ruoxitest
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ENAME,
JOB "lower(:job)",
sal "to_number(:sal)"
)
LOAD DATA
APPEND INTO TABLE RUOXITEST
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523
在F:oracleproduct10.2.0client_2NETWORKADMIN的tns文件中，指定了相同的service name的监听路径。
Record 3: Rejected - Error on table RUOXITEST, column TEST.
[root@localhost oracle]# dos2unix Book1.csv
dos2unix: converting file Book1.csv to UNIX format ...
[oracle@localhost oracle]$ sqlldr scott/@grs control=case1.ctl
成功
4.1 增加errors参数
>sqlldr scott/cxxxx@orcl control=xxxxx.ctl errors=10
明确指定出现错误到10次就停止加载
4.2 指定rows参数
sqlldr常规路径导入默认是一次 64行，可以适当增加rows
rows=640
有可能rows的值超过了bindsize的值，bingsize的默认值256K，
>sqlldr scott/cxxxx@orcl control=xxxxx.ctl errors=10 rows=5000 bindsize=
Bindsize 10M(1024*1024*10) =
4.3使用直接路径加载direct
>sqlldr scott/cxxxx@orcl control=xxxxx.ctl direct=true
直接路径加载默认是读取全部记录，不需要rows参数，
直接路径主要有2个参数：
streamsize 读取到的数据存入流缓存区
streamsize 10M(1024*1024*10) =
date_cache指定一个转换后日期格式的缓冲区，以条为单位，默认1000，如有有导入的日期列
date_cache =5000
>sqlldr scott/cxxxx@orcl control=xxxxx.ctl direct=true Streamsize= date_cache=5000
外部表导数据的限制：数据文件必须在服务器上，或在服务器上访问的输入文件。
多个用户并发的使用相同的外部表来处理不同的输入文件
sqlldr scott/xx#orcl demo1.ctl external_table=generate_only
External_table有3个参数值，
Not_used,默认值
Execute,说明sqlldr不会生成并行执行一个sql insert语句，而是会创建一个外部表，并使用一个批量sql语句来加载
Generate_only,sqlldr不加载任何数据，只是生成所执行的sql ddl和dml语句，并放到它创建的日志文件中
1 创建一个目录
conn /as sysdba
create or replace directory xxx as ‘f:sqlldr’
grangt read,write on directory xxx to scott
2创建外部表
Direct=true会覆盖external_table=generate_only，
:sqlldr>sqlldr scott/@orcl control=1024TEST.CTL external_table=generate_only
会根据控制文件中生成一个log文件
A 首先会创建一个目录
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'F:sqlldr'
B 创建外部表
CREATE TABLE statement for external table:
------------------------------------------------------------------------
1 type：oracle_loader传统方式
Oracle_dump数据泵
2 DEFAULT DIRECTORY 指定数据文件所在路径对于的directory的名称
3 Records 该字句指定记录结束标记 默认为：RECORDS DELIMITED BY NEWLINE
4 Badfile 错误文件名和路径
5 Logfile 日志文件名
6 Readsize oracle读取输入数据文件所用的默认缓冲区，READSIZE =1m
7 Skip 跳过的记录数
8 FIELDS TERMINATED BY ","
9 REJECT ROWS WITH ALL NULL FIELDS 该字句表示如果要加载的字段的所有行都是空值，则外部表并不执行加载，
10 Location 用来指定来源数据，
11 REJECT LIMIT UNLIMITED 用来接受查询数据时能够接受的错误数，不指定默认是0，UNLIMITED 表示不限制
INSERT statements used to load internal tables:
------------------------------------------------------------------------
然后手动执行sql
查看日志或错误日志
5.3 使用外部表加载不同的文件
alter table xxxx location(‘xxxx.ctl’,’xxxx.dat’)
5.4 多用户问题
alter table xxxx location(‘xxxx1.dat’,’xxxx.dat’)
5.5 外部表加载的效率
主要由三方面 CPU,CACHE,I/O
CPU 对于cpu，只要空闲，oracle就会利用它
I/O 需要dba认真规划，是否启用了归档，并行等，对io影响最常见的调整方式
Paralled 设置并行参数
Access parameters中显示指定nologfile，nobadfile，nodisfile等降低磁盘io
CACHE 中，access parameters中2个参数，bindsize跟date_cache
1 首先创建一个目录
Create or replace directory as xxx ‘f:mydb’
create directory tmp as 'f:mydb'
2 然后准备一个简单的select语句向这个目录中卸载数据
3 把allobjects.dat 改数据文件移植到另外一个服务器，然后在提取此ddl
然后 insert /*+ append */ into some_table select * from all_objects_unload;
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/14810.html