SQLLoader,所有文件都在客户端,不在服务器端。
分两种模式
一种有控制文件
二种没有控制文件(SQLLoader Express模式)
1、在服务器上建立表
create table test
( region char(3),
region_name varchar2(12),
bill_month number(6),
fee number(10,2)
);
2、在客户端建立一个test.dat文件
SQL> host cat test.dat
cat: test.dat: 没有那个文件或目录
SQL> !vi test.dat
530,HZ,200501,100.01
530,HZ,200502,800.23
531,JN,200501,5000.81
531,JN,200502,5360.00
532,QD,200501,20670.32
532,QD,200502,22000.08
533,ZB,200501,3050.56
533,ZB,200502,3108.14
SQL> host cat test.dat
530,HZ,200501,100.01
530,HZ,200502,800.23
531,JN,200501,5000.81
531,JN,200502,5360.00
532,QD,200501,20670.32
532,QD,200502,22000.08
533,ZB,200501,3050.56
533,ZB,200502,3108.14
[oracle@host01 ~]$ pwd
/home/oracle
[oracle@host01 ~]$ ls -l test.dat
-rw-r–r-- 1 oracle oinstall 176 9月 29 14:12 test.dat
SQL> host sqlldr hr/hr@orcl TABLE=test;
[oracle@host01 ~]$ sqlldr hr/hr@orcl TABLE=test;
SQL*Loader: Release 18.0.0.0.0 - Production on Wed Sep 29 14:17:48 2021
Version 18.3.0.0.0
Copyright © 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: TEST
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
SQLLoader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file test.dat
ORA-01031: insufficient privileges
SQLLoader-579: switching to direct path for the load
SQLLoader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQLLoader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: TEST
Path used: Direct
Load completed - logical record count 8.
Table TEST:
8 Rows successfully loaded.
Check the log file:
test.log
SQL> select * from test;
REGION REGION_NAME BILL_MONTH FEE
530 HZ 200501 100.01
530 HZ 200502 800.23
531 JN 200501 5000.81
531 JN 200502 5360
532 QD 200501 20670.32
532 QD 200502 22000.08
533 ZB 200501 3050.56
533 ZB 200502 3108.14
8 rows selected.
3、sqlldr后自动有生产一个文件test.log
里面含有如下部分,可以复制到一个文件内容,名为test.ctl
LOAD DATA
INFILE ‘test’
APPEND
INTO TABLE TEST
FIELDS TERMINATED BY “,”
(
REGION,
REGION_NAME,
BILL_MONTH,
FEE
)
所有就可以执行如下指令了,
有控制文件好处虽非Express模式但是ctl中的文件可以自定修改,灵活度高
[oracle@host01 ~]$ sqlldr hr/hr@orcl control=test.ctl
和上面同样效果
.ctl可以扩展如下功能
trailing nullcols是可以为空的,而Express模式是不可以为空的等