Oracle 外部表 External Table(一)[转+实践]

清疚 2022-07-12 14:36 388阅读 0赞

**定义:**

External tables access data in **external sources** as if it were in a table in the database.

You can connect to the database and create metadata for the external table **using DDL**.

The DDL for an external table consists of two parts: **one part that describes the Oracle**

**column types**, and **another part (the access parameters) that describes the mapping of**

**the external data to the Oracle data columns.**

u 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

u 数据在数据库的外部组织，是操作系统文件。

u 操作系统文件在数据库中的标志是**通过一个逻辑目录来映射**的。

u 数据是**只读**的。（外部表相当于一个只读的虚表）

**u ****不可以在上面运行任何 DML 操作**，**不可以创建索引**。

**u ****可以查询操作和连接**。可以并行操作。

**建立外部表的步骤**：

**1、创建以“,”分隔的文件:**

**     ** 在PL/SQL DEVELOP 里,

select \* from emp where deptno=10;  
         select \* from emp where deptno=20;  
         select \* from emp where deptno=30;

用EMP表生成3个部门的数据文件,导出为CSV格式,命名为EMP10.CSV,EMP20.CSV,EMP30.CSV,去掉标题,记得哈...

**2、创建一个Directory：**

create directory TestTable\_diras 'D:/Test' ;

目录问题导致了我折腾了半天多的时间,按照引用的这篇BLOG里的建立情况,应该是没有什么问题,可是不知道自己的操作系统的配置环境有什么问题,我的是,WIN XP ,Oracle9i Enterprise Edition Release 9.2.0.1.0 老是提示以下错误:

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错( a- z% K. Q/ h3 d  A' /  
     ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access cparameters

今天早上发现竟然是目录造成的,网上查了很久没有我的问题的解决方案,突然记得之前也出现类似目录情况,因此试了下,把目录改成create or replace directory TestTable\_diras 'D:/Test/' ; 多了一"/"后就解决了,应该属于个例....郁闷了半天啊..

**3、创建一个外部表：**

(这个表可是用了我一天的时间,GOOGLE了N篇帖子,环境不同(数据库编码,字符集,语言)都会造成相应的问题,测试时通过的,我遇到的主要问题是日期和字符长度)

CREATE TABLE  EXT\_EMP (  
  emp\_id number(4),  
  ename varchar2(12),  
  job varchar2(12) ,  
  mgr\_id  number(4) ,  
  hiredate date,  
  salary number(8),  
  comm number(8),  
  dept\_id number(2)  
  )  
ORGANIZATION EXTERNAL(  
  TYPE ORACLE\_LOADER  
  DEFAULT DIRECTORY DIR\_TEST  
  ACCESS PARAMETERS(  
    RECORDS DELIMITED BY NEWLINE  
    badfile 'bad.txt'  
    FIELDS TERMINATED BY ','  
    optionally enclosed BY '"'  
    missing field VALUES are NULL  
        (  
        emp\_id,  
        ename,  
        job,  
        mgr\_id,  
        hiredate date "YYYY-MM-DD HH24:MI:SS",  
        salary,  
        comm ,  
        dept\_id  
        )  
    )  
LOCATION('emp10.csv','emp20.csv','emp30.csv')  
)REJECT LIMIT UNLIMITED

对上面的SQL中的几个主要部分做个简单的说明：

Ø Create table： 这部分代码与标准的表一样，并且在里面指定字段名等内容，没有特别的地方

Ø ORGANIZATION EXTERNAL: 这个子句就表明现在声明的是一个外部表而不是一张普通的表

Ø DEFAULT DIRECTORY temp:  这个子句指定外部表的文件在哪个目录中取得

Ø Type oracle\_loader: 数据转换驱动器，oracle\_loader为默认，也可以改换其他

Ø Accessparameters: 设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符

Ø RECORDS DELIMITED BY NEWLINE: 这个子句说明文本文件中的每一行就是一个记录

Ø FIELDS TERMINATED BY ',': 这个子句用于表示各个字段间用什么来分隔

Ø MISSING FIELD VALUES ARE NULL: 这个子句说明如果一个记录中某个字段的值没有，则按“空”处理

Ø (....hiredate date "YYYY-MM-DD HH24:MI:SS",.....)这个日期格式一定要完整的把表所有字段都列入其中,否则报错, 它用于指定日期型字段的格式码或许其它字段格式，这个格式码将直接与文件中的格式相对应，这样才能实现数据的正确读取和导入

Ø Optionally enclosed BY  ' " ' :   数据使用"作为数据格式符号

Ø LOCATION('data.txt'): 这个子句用于指明外部文件的文件名+目录名，就能在操作系统中找到外部文件了

**外部表的错误处理**

1、REJECT LIMIT子句

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。

\* 默认的REJECT LIMIT值为0

\* REJECT LIMIT UNLIMITED则不会报错

2、BADFILE 和 NOBADFILE 子句

在accessparameters中加入BADFILE'BAD\_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD\_FILE.txt'中

使用NOBADFILE子句则表示忽略转换错误的数据

● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件

● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。

3、LOGFILE 和 NOLOGFILE 子句

在accessparameters中加入LOGFILE'LOG\_FILE.log'子句，则所有Oracle的错误信息放入'LOG\_FILE.log'中

使用NOLOGFILE子句则表示不记录错误信息到log中

● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件

例如:我修改了EMP20.CSV中的一个月份改成15月,查看相应的日志得到如下信息:

error processing column HIREDATE in row 5 for datafile d:/temp/emp20.csv  
              ORA-01843: not a valid month

同时查看相应的BAD.TXT里可以看到那条错误记录:

"7902","FORD ","ANALYST","7566","1981-15-03","3000.00","","20"

**修改外部表语句**

外部表与堆表一样可以之用ALTER TABLE命令修改表属性

\* REJECT LIMIT        --错误数

\* DEFAULT DIRECTORY   --默认目录

\* ACCESS PARAMETERS   --参数

\* LOCATION            --数据文件

\* ADD COLUMN          --增加列

\* MODIFY COLUMN       --列定义

\* DROP COLUMN         --删除列

\* RENAME TO           --外部表更名

**其他约束** (外部表作为sqlldr的替换产品，在使用范围上，还是有一定的局限性质)

● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。

● 外部表不能建立索引，如要建立，则需要先create table XX as select \* from TestTable

● 外部表不支持多用户的并发修改，也不支持LOB类型，而且访问路径中的文件必须在服务器端

**查看外部表信息:**

SELECT OWNER,TABLE\_NAME,DEFAULT\_DIRECTORY\_NAME,ACCESS\_PARAMETERS  FROM　DBA\_EXTERNAL\_TABLES;  
SELECT  \*  FROM  DBA\_EXTERNAL\_LOCATIONS;

转载地址:

[http://arvinnan.javaeye.com/blog/308638][http_arvinnan.javaeye.com_blog_308638]

[http://xsb.itpub.net/post/419/38484][http_xsb.itpub.net_post_419_38484]

[http_arvinnan.javaeye.com_blog_308638]: http://arvinnan.javaeye.com/blog/308638
[http_xsb.itpub.net_post_419_38484]: http://xsb.itpub.net/post/419/38484