SQL*Loader使用

SQL*Loader

1.数据载入方法

默认是常规加载，如果要使用直接路径加载，只需要将控制文件添加direct=true即可。

2. 控制文件写法

load data

infile 'test.dat' --指定加载的数据文件，可以是单独的文件名/相对路径/（控制文件和加载文件在一起时），也可以带有目录的文件名/绝对路径/ 控制文件和加载的数据文件不在一个目录下

into table table_name --table_name 表名  

fields terminated by ',' optionally enclosed by '"'

(,dname,loc) --列名

3. 其他参数

参数	说明
insert	为缺省方式，在数据装载开始时要求表为空
append	在表中追加新记录
replace	删除旧记录(用 delete from table 语句)，替换成新装载的记录
truncate	删除旧记录(用 truncate table 语句)，替换成新装载的记录

4. 实验

4.1 不带列头的加载

• 创建目录

  [oracle@oracle ~]$ mkdir -p /home/oracle/sqlldr
  

• 创建表格

  在HR用户下创建表格

  SQL> conn hr/hr;
  Connected.
  SQL> create table dept(deptno number(5),dname varchar2(15),loc varchar2(15));
  
  Table created.
  
  SQL>
  

• 创建数据文件

  [oracle@oracle ~]$ cd /home/oracle/sqlldr
  [oracle@oracle sqlldr]]$ vi test.dat10,"A","CHINA"
  11,"B","USA"
  12,"C","RUSSIA"
  13,"D","INDIA"
  14,"E","KOREA"
  15,"F","JAPAN"
  16,"G","FRANCE"
  17,"H","ENGLISH"
  

• 创建控制文件

  [oracle@oracle sqlldr]$ vi test.ctl
  
  load data
  
  infile 'test.dat'
  
  into table dept
  
  fields terminated by ',' optionally enclosed by '"'
  
  (deptno,dname,loc)
  

• 加载数据

  [oracle@oracle sqlldr]$ sqlldr hr/hr control=test.ctl log=test.log
  
  SQL*Loader: Release 12.2.0.1.0 - Production on Sat Dec 10 21:35:20 2022
  
  Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
  
  Path used:      Conventional
  Commit point reached - logical record count 8
  
  Table DEPT:
    8 Rows successfully loaded.
  
  Check the log file:
    test.log
  for more information about the load.
  [oracle@oracle sqlldr]$
  

4.2 带列头的加载

• 创建数据文件

  [oracle@oracle sqlldr]$ vi test01.dat
  
  deptno,dname,loc
  10,"A","CHINA"
  11,"B","USA"
  12,"C","RUSSIA"
  13,"D","INDIA"
  14,"E","KOREA"
  15,"F","JAPAN"
  16,"G","FRANCE"
  17,"H","ENGLISH"
  

• 创建控制文件

  添加field names all files ignore即可忽略数据文件表头（列名）

  [oracle@oracle sqlldr]$ vi test01.ctl
  
  load data
  
  field names all files ignore
  
  infile 'test01.dat'
  
  into table dept
  
  fields terminated by ',' optionally enclosed by '"'
  
  (deptno,dname,loc)
  

• 加载数据

  [oracle@oracle sqlldr]$ sqlldr hr/hr control=test01.ctl log=test01.log
  
  SQL*Loader: Release 12.2.0.1.0 - Production on Sat Dec 10 21:41:36 2022
  
  Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
  
  Path used:      Conventional
  Commit point reached - logical record count 8
  
  Table DEPT:
    8 Rows successfully loaded.
  
  Check the log file:
    test01.log
  for more information about the load.