SQL*Loader与外部表-优快云博客

本文链接：https://blog.youkuaiyun.com/zq9017197/article/details/6845294

sqlldr是把数据加载到数据库中；外部表中的数据是基于操作系统文件的，真正的数据没有保存到数据库中，是在操作系统文件里面的。所以外部表里面的数据只可以select。外部表基本上可以替代sqlldr。但是在以下3种情况下，应该选择sqlldr而不是外部表：

1.必须通过网络加载数据，也就是说，输入文件不在数据库服务器上。

2.多个用户必须并发的使用相同的外部表来处理不同的输入文件。

3.必须使用LOB类型。外部表不支持LOG。

利用sqlldr的例子来生成外部表的脚本

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo1.ctl 
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
ABC,XYZ,Hello
[oracle@linux sqlldr]$ sqlldr ing/ing demo1.ctl external_table=generate_only

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

查看生成的log文件

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo1.log 

SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

控制文件:      demo1.ctl
数据文件:      demo1.ctl
  错误文件:    demo1.bad
  废弃文件:    未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续:    未作指定
所用路径:       外部表

表 DEPT,已加载从每个逻辑记录
插入选项对此表 INSERT 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER            
DNAME                                NEXT     *   ,       CHARACTER            
LOC                                  NEXT     *   ,       CHARACTER            



文件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/sqlldr'
--sqlldr连接到数据库，查询数据字典。没有找到合适的目录，就创建一个SYS_SQLLDR_XT_TMPDIR_00000目录。

用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"      --创建一张表SYS_SQLLDR_X_EXT_DEPT
(
  "DEPTNO" NUMBER(10),
  "DNAME" VARCHAR2(20),
  "LOC" VARCHAR2(20)
)
ORGANIZATION external           --表明这不是一张普通表，而是一张外部表。
(
  TYPE oracle_loader            --加载数据的类型。另外还有一个10G才支持的ORACLE_DATAPUMP可以用来加载或卸载数据。
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000            --使用前面创建的目录SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK    --记录默认也换行符结束。
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'      --在刚创建的目录中记录一个坏文件，无法加载的记录会记录到此文件中。
    LOGFILE 'demo1.log_xt'                                --日志文件。
    READSIZE 1048576                                      --加载数据的缓存区大小。1024*1024=1048576
    SKIP 6                                                --跳过前面6行不加载，因为前面6行是sqlldr控制文件的信息。
    FIELDS TERMINATED BY "," LDRTRIM                      --数据以逗号分隔，LDRTRIM表示去掉前后的空白字符。
    REJECT ROWS WITH ALL NULL FIELDS                      --外部表会在坏文件中记录全空的行，而不加载这些行。
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location 
  (
    'demo1.ctl'        --告诉oracle所加载的文件的文件名。
  )
)REJECT LIMIT UNLIMITED


用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT   --用于从外部表本身直接加载数据。 
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT 
  "DEPTNO",
  "DNAME",
  "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"


用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"            --删除外部表。
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000     --删除目录。



从 星期二 10月 04 21:59:06 2011 开始运行
在 星期二 10月 04 21:59:06 2011 处运行结束

经过时间为: 00: 00: 00.14
CPU 时间为: 00: 00: 00.04

下面就执行生成的脚本

SQL> conn /as sysdba
已连接。
SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/sqlldr';

目录已创建。

SQL> grant read,write on directory SYS_SQLLDR_XT_TMPDIR_00000 to ing;

授权成功。

SQL> conn ing/ing
已连接。
SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT" 
  2  (
  3    "DEPTNO" NUMBER(10),
  4    "DNAME" VARCHAR2(20),
  5    "LOC" VARCHAR2(20)
  6  )
  7  ORGANIZATION external 
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
 11    ACCESS PARAMETERS 
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'
    LOGFILE 'demo1.log_xt'
    READSIZE 1048576
      "DEPTNO" CHAR(255)
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
 23        "DNAME" CHAR(255)
 24          TERMINATED BY ",",
 25        "LOC" CHAR(255)
 26          TERMINATED BY ","
 27      )
 28    )
 29    location 
 30    (
 31      'demo1.ctl'
 32    )
 33  )REJECT LIMIT UNLIMITED;

表已创建。

查询外部表

SQL> select * from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 Sales                Virginia
        20 Accounting           Virginia
        30 Consulting           Virginia
        40 Finance              Virginia

此时到目录下面去查看bad文件会发现存在一条记录，正是无法加载到数据库中的记录，因为ABC无法转换问数字。

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ ll | grep demo1
-rw-r--r-- 1 oracle oinstall   14 10-04 22:15 demo1.bad
-rw-r--r-- 1 oracle oinstall  187 10-04 21:58 demo1.ctl
-rw-r--r-- 1 oracle oinstall 2319 10-04 21:59 demo1.log
-rw-r--r-- 1 oracle oinstall  570 10-04 22:15 demo1.log_xt
[oracle@linux sqlldr]$ cat demo1.bad 
ABC,XYZ,Hello

使用外部表加载不同的文件

首先另行准备一个数据文件（故意空了6行，因为我们定义外部表的时候指定了SKIP 6嘛）

[oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo2.data 






50,Sales,Virginia
60,Accounting,Virginia
70,Consulting,Virginia
80,Finance,Virginia

然后执行命令

SQL> alter table SYS_SQLLDR_X_EXT_DEPT location('demo2.data');

表已更改。

SQL> select * from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        50 Sales                Virginia
        60 Accounting           Virginia
        70 Consulting           Virginia
        80 Finance              Virginia

oracle外部表！！！