oracle外部表！！！

来源：互联网发布：天天向上网络作家专场编辑：程序博客网时间：2024/05/02 06:12

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

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

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

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

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

[oracle@linux sqlldr]$ pwd/u01/sqlldr[oracle@linux sqlldr]$ cat demo1.ctl LOAD DATAINFILE *INTO TABLE DEPTFIELDS TERMINATED BY ','(DEPTNO,DNAME,LOC)BEGINDATA10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance,VirginiaABC,XYZ,Hello[oracle@linux sqlldr]$ sqlldr ing/ing demo1.ctl external_table=generate_onlySQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011Copyright (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 2011Copyright (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.14CPU 时间为: 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,Virginia60,Accounting,Virginia70,Consulting,Virginia80,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