Oracle 外部表（External Table）的用法与SQLLDR

对于外部表（External Table），数据库中只保存了表结构信息，而数据存放在文件系统上的外部文件中，所以外部表只能被只读访问。

下面通过一个实例来介绍一下外部表的用法。

要使用外部表首先要创建目录（在Oracle 9i之前目录需要通过utl_file_dir静态参数来设置）并授权，目录是数据文件的存放地点，数据文件是指外部表要读取的文件，通常是文本文件。

SQL> connect / as sysdba
Connected.
SQL> create or replace directory sqldr
  2  as '/opt/oracle/sqldr';
Directory created.
SQL> grant read,write on directory sqldr to eygle;
Grant succeeded.
SQL> col DIRECTORY_PATH for a30
SQL> col DIRECTORY_NAME for a10
SQL> select * from dba_directories where DIRECTORY_NAME='SQLDR';
OWNER                          DIRECTORY_ DIRECTORY_PATH
------------------------------ ---------- ------------------------------
SYS                            SQLDR      /opt/oracle/sqldr
复制

然后就可以创建外部表了，以下是一个外部表的创建范例，其关键字在于ORGANIZATION external。

CREATE TABLE "USERS"
  (  
  USERNAME 	VARCHAR2(30),
  USER_ID 	NUMBER,
  PASSWORD	VARCHAR2(30)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SQLDR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'SQLDR':'users.bad'
    DISCARDFILE 'SQLDR':'users.dis'
    LOGFILE 'SQLDR':'users.log'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      USERNAME CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      USER_ID  CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      PASSWORD CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location('data.txt')
)
REJECT LIMIT UNLIMITED
复制

创建完成之后，就可以通过SQL语句访问这个外部表中的数据了：

SQL> select * from users;
USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
SYS                                     0 8A8F025737A9097A
SYSTEM                                  5 D4DF7931AB130E37
OUTLN                                  11 4A3BA55E08595C81
SCOTT                                  38 F894844C34402B67
ORACLE                                 45 EXTERNAL
PERFSTAT                               47 AC98877DE1297365
EYGLE                                  41 B726E09FE21F8E83
DBSNMP                                 19 E066D214D5421CCC
WMSYS                                  21 7C9BA362F8314299

9 rows selected.
复制

如果大家注意一下外部表的创建语句，你会发现这与我们熟悉的SQLLDR语法非常相似。

下面来简要介绍一下SQLLDR的用法，使用SQLLDR首先需要创建一个控制文件，通过控制文件可以将数据很容易地加载入数据库中：

LOAD 
INFILE '/opt/oracle/sqldr/data.txt' 
badfile '/opt/oracle/sqldr/users.bad' 
discardfile '/opt/oracle/sqldr/users.dis' 
APPEND 
INTO TABLE users
fields terminated by ',' optionally enclosed by '"' 
trailing nullcols 
( username  char(30), 
  user_id   char(30),
  password  char(30)
)
复制

加载过程很简单：

[oracle@jumper sqldr]$ sqlldr eygle/eygle control=user.ctl
SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:51:35 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Commit point reached - logical record count 9
复制

现在这些数据就已经被加载到数据库中了。
在Oracle 9i中，SQLLDR增加了一个新的参数external_table。

[oracle@jumper sqldr]$ sqlldr |grep external
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
复制

通过这个参数的GENERATE_ONLY选项，可以生成完整的外部表创建语句：

[oracle@jumper sqldr]$ sqlldr eygle/eygle control=user.ctl external_table=GENERATE_ONLY

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:48:22 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
复制

这个操作只是生成了外部表维护语句，并不会真正地加载数据。现在检查user.log文件，这个文件包括了非常详细的内容：

SQL*Loader: Release 9.2.0.4.0 - Production on Mon Mar 19 16:58:03 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
复制

首先是列举控制文件的相关信息：

Control File:   user.ctl
Data File:      /opt/oracle/sqldr/data.txt
  Bad File:     /opt/oracle/sqldr/users.bad
  Discard File: /opt/oracle/sqldr/users.dis 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table USERS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
USERNAME                            FIRST    30   ,  O(") CHARACTER            
USER_ID                              NEXT    30   ,  O(") CHARACTER            
PASSWORD                             NEXT    30   ,  O(") CHARACTER            
复制

接下来是自动生成的创建目录的脚本，目录名称是按规则自动生成的，路径则指向当前数据文件的路径：

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/opt/oracle/sqldr/'
复制

然后是创建外部表的完整语句：

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_USERS" 
(
  USERNAME VARCHAR2(30),
  USER_ID NUMBER,
  PASSWORD VARCHAR2(30)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'users.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'users.dis'
    LOGFILE 'user.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      USERNAME CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      USER_ID CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      PASSWORD CHAR(30)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location 
  (
    'data.txt'
  )
)REJECT LIMIT UNLIMITED
复制

下面是加载数据的INSERT语句，可以通过INSERT语句将数据转移到数据库的内部表中：

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO USERS 
(
  USERNAME,
  USER_ID,
  PASSWORD
)
SELECT 
  USERNAME,
  USER_ID,
  PASSWORD
FROM "SYS_SQLLDR_X_EXT_USERS"
复制

最后完成整个过程，外部表和目录可以被删除：

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_USERS"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Run began on Mon Mar 19 16:58:03 2007
Run ended on Mon Mar 19 16:58:03 2007

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.05
复制

通过这个例子可以看到，外部表实际上就是通过SQLLDR的接口驱动来完成外部数据访问的，Oracle的外部表实际上是对SQLLDR功能的进一步扩展和增强。