使用外部表

目录：

1、CREATE TABLE 语句创建

2、SQL Loader创建

3、外部表参数

4、修改外部表

1、CREATE TABLE语句创建

（1）创建Directory 对象，并授予用户读写Directory的权限

SQL> CREATE DIRECTORY EXTDIR AS '/home/oracle/text';

Directory created.

SQL> GRANT READ,WRITE ON DIRECTORY EXTDIR TO SCOTT;

Grant succeeded.

（2）执行Create table 语句

使用SQL*Loader 的一个控制文件，作为外部表的数据源，Directory对象使用前面创建的EXTDIR。

文件：ldr_case2.dat

SMITH,CLEAK,3904
ALLEN,"SALER,M",2891
WARD,"SALER,""S""",3128
KING,PRESIDENT,2523

________________________________

SQL> CREATE TABLE EXT_CASE
   (ENAME VARCHAR2(10),
    JOB VARCHAR2(20),
    SAL  NUMBER)
    ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
    DEFAULT DIRECTORY EXTDIR
    ACCESS PARAMETERS
    (
     RECORDS DELIMITED BY NEWLINE
     BADFILE 'EXTDIR' : 'ldr_case.bad'
     LOGFILE 'EXTDIR' : 'ldr_case.log'
     SKIP 1
     FIELDS TERMINATED BY ","
     OPTIONALLY ENCLOSED BY '"'
     REJECT ROWS WITH ALL NULL FIELDS
     (ENAME,JOB,SAL)
   )
   LOCATION('ldr_case2.dat')
   );


Table created.


SQL> select * from ext_case;


ENAME      JOB                         SAL
---------- -------------------- ----------
ALLEN      SALER,M                    2891
WARD       SALER,"S"                  3128
KING       PRESIDENT                  2523

2、SQL Loader创建

SQLLDR 有一个参数：external_table，该参数有三个属性值：

       NOT_USED：不使用外部表，通过常规路径或直接路径加载数据，也是该参数的默认值。

       GENERATE_ONLY：SQLLDR并不执行加载，而是生成创建外部表的SQL和处理数据的SQL，并保存在log文件中。DBA可以修改后拿到SQL*Plus中执行。   

       EXECUTE：首先创建外部表，然后通过外部表方式加载数据

[oracle@oracledb text]$ sqlldr scott/tiger control=ldr_case2.ctl external_table=generate_only;

在目录下查看日志文件ldr_case2.log，发现与创建外部表相关脚本

CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS" 
(
  "ENAME" VARCHAR2(10),
  "JOB" VARCHAR2(10),
  "SAL" NUMBER
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY EXTDIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'EXTDIR':'ldr_case2.bad'
    LOGFILE 'ldr_case2.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "ENAME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "JOB" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SAL" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'ldr_case2.dat'
  )
)REJECT LIMIT UNLIMITED

该脚本只能够用做参考，直接执行不了，修改表名，目录名，结尾加上分号才行。

3、外部表参数

   第一部分：指定表结构

    如同常规建表的方式一样，先创建表名，列名，定义字段类型。

    ORGANIZATION  exernal 则指定这是一个外部表。

 

   第二部分：指定访问驱动的路径

  （1）TYPE：有两个选择

     ORACLE_LOADER：采用SQLLDR方式，参数众多，应用广泛。

     ORACLE_DATAPUMP ：采用数据泵（Datapump），这是11gR2中新增的访问方式，但是数据文件采用数据泵产生的专有二进制文件（DMP文件）。但是既可以加载外部表，也可以生成外部表。

 （2）DEFAULT DIRECTORY：指定数据文件所在路径对应的Directory对象名称。

 

  第三部分：指定加载参数

  加载参数决定了外部表如何访问数据文件。具体需要哪些参数，这要看采用的那种访问驱动，不同的驱动有不同的加载参数。

  使用SQLLDR命令生成的是ORACLE_LOADER的加载参数。常用的参数介绍如下：

 （1）RECORDS：该子句指定记录结束标记，默认是RECORDS DELIMITED BY NEWLINE，即以换行符结束。

 （2）BADFILE：错误文件名称和Directory对象名。

 （3）LOGFILE：日志文件名称和Directory对象名。

 （4）READSIZE：（优化）读取日志文件的缓冲区大小，默认是1MB，与SQLLDR命令中的同参数具有相同功能。

 （5）SKIP：跳过的记录数。（ 注意：不能有SQL*Loader中的 LOAD 参数限制加载的行数）

 （6）FIELDS TERMINATED BY 

 （7）OPTIONALLY ENCLOSED BY '"'

 （8）REJECT ROWS WITH ALL NULL FIELDS：该子句表示如果要加载的行所有字段均为空值，则外部表不加载，如果不指定则会加载空行。

  第四部分：指定数据来源

（1） LOCATION子句用来指定数据来源，语法就是LOCATION('filename1','filename2'...)可以同时指定多个数据来源文件，按序读入

（2）REJECT LIMIT 子句，用来指定查询数据时能够接受的错误数，如果不指定默认是0，即不允许出现错误，UNLIMITED则是不限制。

4、修改外部表

    外部表的修改受到很大的限制。只有部分属性能够修改。如上介绍，外部表分为了4个部分，有些时候你你想修改单独的属性是不可以的，要修改必须整个部分都修改。

 第一部分    表结构： 这部分修改与操作普通表完全相同，你可以添加列，删除列、修改列，不过需要注意，对列修改后，ACCESS PARAMETERS子句中也需要相应修改。

 第二部分    访问驱动：一般不需要修改，如果你要修改Directory对象，则语法如下：

                      SQL> ALTER TABLE EXT_CASE DEFAULT DIRECTORY EXTDIR;

 第三部分     加载参数：这部分很复杂，无法单独修改。也就是说，你要修改ACCESS PARAMETERS 中任何参数，你都需要重写一遍ACCESS PARAMETERS 子句。

SQL> ALTER TABLE EXT_CASE ACCESS PARAMETERS (
  2  RECORDS DELIMITED BY NEWLINE
  3    BADFILE 'EXTDIR' : 'ldr_case.bad'
  4    LOGFILE 'EXTDIR' : 'ldr_case.log'
  5    SKIP 1
  6    FIELDS TERMINATED BY ","
  7    OPTIONALLY ENCLOSED BY '"'
  8    REJECT ROWS WITH ALL NULL FIELDS
  9    (ENAME,JOB,SAL)
 10  );

 第四部分：加载路径：可以直接修改

    ALTER TABLE EXT_CASE LOCATION ('ldr_case2.dat');