Oracle 之 外部表

时间：2023-12-25 14:55:49

一、外部表概述

外部表只能在Oracle 9i 之后来使用。简单地说，外部表，是指不存在于数据库中的表。
通过向Oracle 提供描述外部表的元数据，我们可以把一个操作系统文件当成一个只读的数

据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的
延伸。

二、外部表的特性

位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL 语句来完成，而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的，因此在外部表不能够执行DML 操作，也不能创建索引。
ANALYZE 语句不支持采集外部表的统计数据，应该使用DMBS_STATS 包来采集外部表的统计
数据。

三、创建外部表的注意事项

（一）需要先建立目录对象

在建立对象的时候，需要小心，Oracle 数据库系统不会去确认这个目录是否真的存在。
如果在输入这个目录对象的时候，不小心把路径写错了，那可能这个外部表仍然可以正常建

立，但是却无法查询到数据。由于建立目录对象时，缺乏这种自我检查的机制，为此在将路
径赋予给这个目录对象时，需要特别的注意。另外需要注意的是路径的大小写。在Windows

操作系统中，其路径是不区分大小写的。而在Linux 操作系统，这个路径需要区分大小写。
故在不同的操作系统中，建立目录对象时需要注意这个大小写的差异。

（二）对于操作系统文件的要求

建立外部表时，必须指定操作系统文件所使用的分隔符号。并且该分隔符有且只有一个。
创建外部表时，不能含有标题列。如果这个标题信息与外部表的字段类型不一致（如字段内

容是number 数据类型，而标题信息则是字符型数据，则在查询时就会出错）。如果数据类型
恰巧一致的话，这个标题信息Oracle 数据库也会当作普通记录来对待。

当Oracle 数据库系统访问这个操作系统文件的时候，会在这个文件所在的目录自动创建一
个日志文件。无论最后是否访问成功，这个日志文件都会如期建立。查看这个日志文件，可

以了解数据库访问外部表的频率、是否成功访问等等。默认情况下，该日志在与外部表的相
同directory 下产生。

（三）在建立临时表时的相关限制

对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名
称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不
在数据库中存储外部表的数据。
简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。

而没有存储实际的数据。
由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML 时也不支持对外部
表的插入、更新、删除等操作。

（四）删除外部表或者目录对象

一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除
所有表之后再删除目录对象。
如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到
"对象不存在"的错误信息。
查询dba_external_locations 来获得当前所有的目录对象以及相关的外部表，同时会
给出这些外部表所对应的操作系统文件的名字。如果只是在数据库层面上删除外部表，并
不会自动删除操作系统上的外部表文件。

（五）对于操作系统平台的限制

不同的操作系统对于外部表有不同的解释和显示方式
如在Linux 操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows
操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响

四、两种外部表

Oracle 为外部表提供了两种访问的驱动程序，默认的访问程序是oracle_loader,它允许使
用oracle 的装载技术从外部文件中读取数据。另一种访问驱动程序是oracle_datapump 它从

数据库读取数据库并将数据插入到外部表中。

（一）Oracle_loader驱动程序

1．创建datapump 外部表的语法实例：

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW" (

"DEPTNO" NUMBER(2),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13)

)

ORGANIZATION external

(

TYPE oracle_loader --指定外部表的访问方式，9i 不支持oracle_datapump

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ACCESS PARAMETERS --配置外部表参数

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --记录以换行为结束

BADFILE SYS_SQLLDR_XT_TMPDIR_00000:'demo1.bad'--存放处理失败的记录文件描述

LOGFILE 'demo1.log_xt' --日志文件

READSIZE 1048576

--Oracle 读取输入数据文件所用的默认缓冲区,此处为MB

SKIP 6 --跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过

FIELDS TERMINATED BY "," LDRTRIM --描述字段的终止符

REJECT ROWS WITH ALL NULL FIELDS --所有为空值的行被跳过并记录到bad file.

( --下面是描述外部文件各个列的定义

"DEPTNO" CHAR(255) TERMINATED BY ",",

"DNAME" CHAR(255) TERMINATED BY ",",

"LOC" CHAR(255) TERMINATED BY ","

)

)

location

(

'demo1.ctl' --描述外部文件的文件名

)

)REJECT LIMIT UNLIMITED --描述允许的错误数，此处为无限制

/

2．示例

创建外部表去访问/home/oracle 下stu.dat 文件中的记录

//在数据库上创建目录

SQL> create or replace directory dir as '/home/oracle';

//构造数据

[oracle@oracle ~]$ vim stu.dat

000001,ALICE

000002,JACK

000003,DAVID

000004,MIKE

000005,KEVIN

//创建外部表

SQL> create table stu

(id number,name varchar(10))

organization external

(type oracle_loader

default directory dir

access parameters

(

records delimited by newline

badfile dir:'exp.bad'

logfile dir:'emp.log'

fields terminated by ','

missing field values are null

(id,name))

location ('stu.dat'))

reject limit unlimited;

//检索数据

SQL> select * from stu;

ID NAME

---------- ----------

1 ALICE

2 JACK

3 DAVID

4 MIKE

5 KEVIN

（二）Oracle_datapump驱动程序

创建一个外部表去访问另一个数据库中的数据库，先把令一个数据库中的数据导入一个
文件，然后再去访问外部文件。

1．创建datapump 外部表的语法实例：

create table ex_tb1 --创建外部表

(ename,job,sal,dname) --表列描述，注意未指定数据类型

organization external

(

type oracle_datapump

--使用datapump 将查询结果填充到外部表,此处由select 生成不支持oracle_loader

default directory dat_dir --指定外部表的存放目录

location('tb1.exp，tb2.exp'))

parallel 2

--按并行方式来填充，这里的并行度必须与生成的文件数量一致才能起作用

as

select ename,job,sal,dname --填充使用的原始数据

from emp join dept

on emp.deptno=dept.deptno

2．示例：

数据库orcl 要访问数据库prod 中一个表emp 中的部分数据，步骤如下：

--//在prod 数据库上创建目录

SQL> create or replace directory dir as '/home/oracle';

Directory created.

--//对scott 用户授权

SQL> grant write,read on directory dir to scott;

Grant succeeded.

--//解锁scott 用户

SQL> alter user scott identified by oracle account unlock;

User altered.

--//连接scott 用户并且创建外部表

SQL> conn scott/oracle;

Connected.

--//创建外部表

SQL> CREATE TABLE ext_emp

organization external

(

type oracle_datapump

default directory dir

location('emp.dat')

)

as

SELECT ename,empno,deptno

FROM emp;

--//连接到orcl 数据库

[oracle@oracle ~]$ export ORACLE_SID=orcl

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Aug 9 22:23:05 2013

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

--//在orcl 数据库上创建目录

SQL> create or replace directory dir as '/home/oracle';

--//创建外部表

SQL> create table ext_prod_emp(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

DEPTNO NUMBER(2)

)

organization external

(

type oracle_datapump

default directory dir

location('emp.dat')

);

Table created.

SQL> select * from ext_prod_emp;

注：在外部表上不能执行DML 操作

使用CREATE TABLE 语句的ORGANIZATION EXTENERAL 子句来创建外部表。外部表不分配
任何盘区，因为仅仅是在数据字典中创建元数据。

五：创建外部表完整示例：

1：创建文件目录并授权

--切换到 oracle  用户

[root@localhost ~]# su - oracle

--在linux的文件系统上创建 存放外部文件的目录

[oracle@localhost ~]$ cd /home/oracle/

[oracle@localhost ~]$ ls

database  grid         h:1emp.txt  h:1spooltest.txt  oracle_system_files_back  rlwrap-0.37

Desktop   h:1dept.sql  h:1.lst     h:emp.txt         oyt.lst                   rlwrap-0.37.tar.gz

[oracle@localhost ~]$ mkdir extertablefile;

[oracle@localhost ~]$ ls

database  extertablefile  h:1dept.sql  h:1.lst           h:emp.txt                 oyt.lst      rlwrap-0.37.tar.gz

Desktop   grid            h:1emp.txt   h:1spooltest.txt  oracle_system_files_back  rlwrap-0.37

[oracle@localhost ~]$ cd extertablefile/

[oracle@localhost extertablefile]$ pwd

/home/oracle/extertablefile

[oracle@localhost extertablefile]$ ls

[oracle@localhost extertablefile]$ ll

total 0

--连接 oracle数据库

[oracle@localhost extertablefile]$ rlwrap sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 14 18:26:17 2018

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

---查看数据库实例信息

SYS@orcl> select instance_name ,status from v$instance;

INSTANCE_NAME    STATUS

---------------- ------------

orcl             OPEN

---创建 exterdir  外部文件目录

SYS@orcl> create directory exterdir as '/home/oracle/extertablefile';

Directory created.

---给 scott用户赋予创建 文件目录的权限

SYS@orcl> grant create any directory to scott;

Grant succeeded.

---连接 scott用户

SYS@orcl> conn scott/scott

Connected.

---创建文件目录：exterdirscott

SCOTT@orcl> create directory exterdirscott as '/home/oracle/extertablefile';

Directory created.

SCOTT@orcl> conn / as sysdba;

Connected.

----给scott 赋予 sys用户创建的 exterdir 文件目录的读取、编写  的权限

SYS@orcl> grant read ,write on directory exterdir to scott;

Grant succeeded.

SYS@orcl>

相关文章