oracle---SQL*Loader导数

一.概述

  当表列都是字符、数字或日期时间数据类型，且输入数据文件只包含分隔字符数据时，SQL* Loader express模式允许您通过只指定表名来快速执行加载。
在快速模式下，不会使用SQLLoader控制文件。相反，SQLLoader使用ALL_TAB_COLUMNS视图中的表列定义来确定输入字段的顺序（column_id）和数据类型(data_type)。对于大多数其他设置，它采用默认值，您可以使用命令行参数重写这些值。

二、sqlldr的使用

0.sqlldr命令的用法：

Usage: SQLLDR keyword=value [,keyword=value,...]
# 查看帮助信息
[oracle@oracle1 ~]$ sqlldr -help

  请注意:命令行参数可以通过位置或关键字指定。前一种情况的一个例子是’sqlldr scott/tiger foo’; 后者的一个例子是’sqlldr control=foo userid=scott/tiger’。位置参数需要放在关键字参数之前，例如,’ sqldr scott/tiger control=foo logfile=log ‘是允许的，但是’sqldr scott/tiger control=foo log ‘不可以。

1.使用SQL*Loader Express Mode

SQL* Loader使用的一些默认值：
（1）如果没有指定数据文件，则在当前目录中查找名为table-name.dat的文件。
（2）字段设置：默认，逗号作为字段分割符；换行符，作为记录分隔符；字段类型居于列类型。
（3）如果表中已经有数据，那么将向表追加新的数据。
（4）如果您没有指定一个数据文件，那么数据、日志和坏文件采用以下默认名称(注意%p被替换为Oracle数据库子进程的进程ID):
  table-name.dat ：数据文件
  table-name.log ： SQL*Loader 日志文件
  table-name_%p.log_xt ：Oracle Database 日志文件 (例如, emp_17228.log_xt)
  table-name_%p.bad ：坏文件

2.简单实践（初步了解）

# 以scott用户登录，然后创建测试表
sqlplus scott/tiger
SCOTT@orcl> create table dept1 as select * from dept where deptno = 50; -- 表dept1无记录
-- 查看表dept1的结构如下：
SCOTT@orcl> desc dept1;
 Name								   Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 DEPTNO 								    NUMBER(2)
 DNAME									    VARCHAR2(14)
 LOC									    VARCHAR2(13)


# 在操作系统当前目录创建两个数据文件
[oracle@oracle1 ~]$ cat dept_1.dat
50,TEST,China
60 HAHA China
[oracle@oracle1 ~]$ cat dept_2.dat
70,MAKE,China

# 使用sqlldr向表dept1导数
[oracle@oracle1 ~]$ sqlldr scott/tiger table=dept1 data=dept_1.dat,dept_2.dat
# 登录数据库验证：
SCOTT@orcl> select * from dept1;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 TEST 	  China
	70 MAKE 	  China

# 执行命令结束之后
# 1.因为没有指定log参数，所以会在当前目录下产生一个名为dept1.log（名称前缀与表名相同）的日志文件，
# 记录了导数过程中的一些信息（例如：导入成功的记录数和失败的记录数及其失败的原因，总耗时，占用CPU时间等等）
# 2.如果在导数过程中存在失败的记录，因为没有指定bad参数，所以会在当前目录下生成一个
# 与失败记录所在数据文件名同前缀的bad文件（例如：这里dept_1.dat数据文件中存在不规范的记录，字段是空格分开，就会生成一个dept_1.bad文件）
# 文件中完整记录了失败的记录。
# 如果不指定csv参数，默认通过csv=without_embedded处理数据
[oracle@oracle1 ~]$ cat dept_1.bad
60 HAHA China
# 3.没有指定errors参数，默认值为50，所以存在导入失败的数据，还能继续进行数据导入，到失败次数超过50，导数就会终止
# 4.当需要导入的数据文件有多个时，需要使用逗号分隔各个数据文件，当然也可以使用通配符来匹配多个数据文件（*:匹配多个字符，?:匹配任意一个字符）
# 例如：清空表dept1中的数据，然后再次导数
SCOTT@orcl> delete from dept1;
2 rows deleted.
SCOTT@orcl> commit;
Commit complete.
# 下面的语句中带有通配符的数据文件也可以不使用单引号包围
[oracle@oracle1 ~]$ sqlldr scott/tiger table=dept1 data='dept_?.dat'

# 如果数据文件的数据格式是CSV且每个值用双引号包围，使用如下命令导数
[oracle@oracle1 ~]$ vim dept_1.dat 
"50","TEST","China"
60 HAHA China
[oracle@oracle1 ~]$ sqlldr scott/tiger table=dept1 data=dept_1.dat csv=with_embedded

# 如果字段是用单引号包围起来的呢？需要使用enclosed_by参数指定字段包围符（单引号需要通过\号转义）
[oracle@oracle1 ~]$ cat dept_1.dat
'50','TEST','China'
60 HAHA China
[oracle@oracle1 ~]$ sqlldr scott/tiger table=dept1 data=dept_1.dat enclosed_by="\'"

### 5,指定字段分隔符，默认以逗号分隔各个字段（先清除表dept1中的数据）
sqlplus scott/tiger 
truncate table dept1;

#查看要导入的数据格式
[oracle@oracle1 ~]$ cat dept_1.dat
50 TEST China
60 HAHA China

#通过参数terminated_by指定字段分隔符为空白字符（whitespace）
[oracle@oracle1 ~]$ sqlldr scott/tiger table=dept1 data=dept_1.dat terminated_by=whitespace

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Sep 18 18:54:15 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Express Mode Load, Table: DEPT1
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO

Table DEPT1:
  2 Rows successfully loaded.

Check the log files:
  dept1.log
  dept1_%p.log_xt
for more information about the load.

# 登录查看
SCOTT@orcl> select * from dept1;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	50 TEST 	  China
	60 HAHA 	  China

sqlldr读取数据文件的过程：
（1）先为每个字段读取值
（2）再判断值格式是否符合字段类型