再上一篇:14.7小结
上一篇:15.1 SQL*Loader
主页
下一篇:15.3平面文件卸载
再下一篇:15.4数据泵卸载
文章列表

15.2外部表

Oracle 9i 10g编程艺术:深入数据库体系结构

外部表在Oracle9i Release 1中首次引入。简单地说,利用外部表,我们可以把一个操作系统文件 当成是一个只读的数据库表。它们不是“实际”表的替代品,也并非用来取代实际表;而只是用作一种简 化加载的工具(在Oracle 10g中还可以简化数据卸载)。
外部表特性首次出现时,我常常称之为“SQLLDR的替代品”。大多数情况下,这种想法还是对的。既 然如此,你可能会奇怪,为什么我们还要在前面花那么多时间来介绍SQLLDR。原因是,SQLLDR的使用历史 已久,有相当多的遗留控制文件都以它为基础。SQLLDR仍是一个常用的工具;这也是许多人很了解和一直 在使用的工具。我们还处在从使用SQLLDR向外部表转变的中间阶段,所以SQLLDR仍很重要。
许多DBA可能不知道:他们对SQLLDR控制文件的了解在使用外部表时也完全可以用上。学习本章这 部分的例子时,你会发现,外部表结合了许多SQLLDR语法和许多SQLLDR技术。
在以下3种情况下,应该选择SQLLDR而不是外部表: 必须通过网络加载数据,也就是说,输入文件不在数据库服务器上。外部表要求必须能在
数据库服务器上访问输入文件,这是外部表的限制之一。
多个用户必须并发地使用相同的外部表来处理不同的输入文件。 必须使用LOB类型。外部表不支持LOB。
在考虑这些情况的前提下,通常我强烈建议使用外部表来得到其扩展功能。SQLLDR是一个相对简单 的工具,能生成一个INSERT并加载数据。其使用SQL的能力仅限于逐行地调用SQL函数。外部表则不受此 限制,可以充分利用所有SQL功能集来加载数据。在我看来,外部表超越SQLLDR的关键功能特性如下:
可以使用复杂的WHERE条件有选择地加载数据。尽管SQLLDR有一个WHEN子句用来选择要 加载的行,但是你只能使用AND表达式和执行相等性比较的表达式,在WHEN子句中不能使用区 间(大于、小于),没有OR表达式,也没有IS NULL等。
能够合并(MERGE)数据。可以取一个填满数据的操作系统文件,并由它更新现有的数据库 记录。
能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分。 使用INSERT更容易地执行多表插入。从Oracle9i开始,通过使用复杂的WHEN条件,可以
用一个INSERT语句插入一个或多个表。尽管SQLLDR也可以加载到多个表中,但是相应的语法
相当复杂。
对于开发新手来说,学习曲线更短。SQLLDR作为“另外一种工具”,除了学习编程语言、开
发工具、SQL语句等之外,还要另外学习。但另一方面,只要开发人员懂SQL,就可以直接将这 些知识应用到批量数据加载,而不必学习一个新工具(SQLLDR)。
记住以上几点,下面来看如何使用外部表。

15.2.1建立外部表

作为外部表的首次简单展示,首先再来运行前面的SQLLDR例子,向DEPT表中加载批量数据。你可能 已经想不起来了,所以下面再次列出我们所用的简单控制文件,如下:
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

目前,作为起步,最容易的方法是使用这个现有的遗留控制文件来提供外部表的定义。以下SQLLDR
命令会为我们的外部表生成CREATE TABLE语句:
[tkyte@desktop tkyte]$ sqlldr / demo1.ctl external_table=generate_only
SQL*Loader: Release 10.1.0.4.0 - Production on Sat Jul 16 17:34:51 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved. [tkyte@desktop tkyte]$
EXTERNAL_TABLE参数有以下3个值: NOT_USED:其含义不言自明,这是默认值。
EXECUTE:这个值说明SQLLDR不会生成并执行一个SQL INSERT语句;而是会创建一个外部 表,并使用一个批量SQL语句来加载。
GENERATE_ONLY:这个值使得 SQLLDR 并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文件中。
警告 DIRECT=TRUE覆盖 EXTENAL_TABLE=GENERATE_ONLY。如果指定了 DIRECT=TRUE,则会加载数据, 而不会生成外部表。
使用GENERATE_ONLY时,可以在demo.log文件中看到以下内容:
CREATE DIRECTORY statements needed for files

------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/tkyte'
我们可能会看到日志文件中有一个CREATE DIRECTORY语句(也可能看不到)。在生成外部表脚本期间 , SQLLDR连接到数据库,并查询数据字典来查看是否已经存在合适的目录。在这个例子中,由于没有合适的 目录,所以SQLLDR为我们生成一个CREATE DIRECTORY语句。接下来,它为外部表生成CREATE TABLE语句 :
CREATE TABLE statement for external table:
------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
"DEPTNO" NUMBER(2), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13)
)

SQLLDR已经登录到数据库;只有这样它才知道这个外部表定义中要用的具体数据类型(例如,DEPTNO
是一个NUMBER(2))。SQLLDR根据数据字典来确定这些数据类型。接下来,我们来看这个外部表定义开始处 的内容:
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ORGANIZATION EXTERNAL子句告诉Oracle:这不是一个“正常”表。我们以前在第10章讨论IOT时
曾经见过这个子句。目前有3种组织类型:HEAP对应“正常”表,INDEX 对应IOT,EXTERNAL对应外部表。 余下的文本则告诉 Oracle 有关这个外部表的更多信息。ORACEL_LOADER类型是目前支持的两种类型之一
(Oracle9i中只支持这一种类型)。另一种类型是 ORACLE_DATAPUMP,这是 Oracle 10g及以上版本中Oracle 的专用数据泵格式。我们将在后面介绍数据卸载一节中讨论这个类型,这种格式不仅可以用于加载数据, 也可以卸载数据。外部表可以用于创建一个数据泵格式文件,再读取这个文件。

下一部分是外部表的ACCESS PARAMETERS部分。在此我们告诉数据库如何处理这个输入文件。看到这 个描述时,应该注意到,这与SQLLDR控制文件非常相似;这绝非偶然。在大多数情况下,SQLLDR和外部 表使用的语法都很相似。
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' LOGFILE 'demo1.log_xt'
READSIZE 1048576
SKIP 7
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS
(
"DEPTNO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "DNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "LOC" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
这些访问参数显示了如何建立一个外部表,使之能像SQLLDR一样几乎以同样的方式处理文件:
RECORDS:记录默认以换行符结束,SQLLDR中的记录就是如此。 BADFILE:在刚创建的目录中建立了一个坏文件(无法处理的记录都记录到这个文件中)。 LOGFILE:在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件。 READSIZE:这是Oracle读取输入数据文件所用的默认缓冲区。在这里是1MB。如果采用占
用服务器模式,这个内存来自PGA,如果采用共享服务器模式,则来自SGA。它用于缓存输入数 据文件中对应一个会话的信息(参见第4 章,其中讨论了PAG和SGA内存)。如果你在使用共享 服务器,要记住一点:内存从SGA分配。
SKIP 7:在确定了应该跳过输入文件中的多少记录。你可能会问:“为什么有’skip 7’? “是这样,在这个例子中我们使用了INFILE *;使用SKIP 7就是跳过控制文件本身来得到内嵌 的数据。如果没有使用INFILE *,就根本不会有SKIP子句。
FIELDS TERMINATED BY:这与控制文件中的用法一样。不过,外部表没有增加LDRTRIM,这 代表LoaDeR TRIM。这是一种截断模式,模拟了SQLLDR截断数据的默认做法。还有另外一些选 项,包括LRTRIM、LTRIM和 RTRIM,表示左截断/右截断空白符;NOTRIM表示保留所有前导/尾 随的空白符。
REJECT ROWS WITH ALL NULL FIELDS:这导致外部表会在坏文件中记录所有全空的行,而 且不加载这些行。
列定义本身:这是有关所期望输入数据值的元数据。它们是所加载数据文件中的字符串, 长度最多可达255个字符(SQLLDR的默认大小),以逗号(,)结束,(还可以选择用引号括起来 )。
注意 要全面了解使用外部表时可用的所有选项,请参考Oracle Utilities Guide手册。这本手册中 有一节专门讨论外部表。Oracle SQL Reference Guide手册提供了基本语法,但是不包括ACCESS PARAMETERS部分的细节。
最后,我们来看外部表定义中的LOCATION部分:
location
(
'demo1.ctl'
)
) REJECT LIMIT UNLIMITED

这告诉Oracle所加载文件的文件名,在这里就是 demo1.ctl,因为我们在原控制文件中使用了INFILE
*。控制文件中的下一条语句是默认的INSERT,可以用于从外部表本身加载表:
INSERT statements used to load internal tables:
------------------------------------------------------------------------ INSERT /*+ append */ INTO DEPT
(

DEPTNO, DNAME,

LOC
)
SELECT
"DEPTNO",
"DNAME", "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"
如果可能,这会执行一个逻辑上与直接路径加载等价的操作(假设可以遵循APPEND提示;如果存在
触发器或外键约束,可能不允许发生直接路径操作)。
最后,在日志文件中,我们会看到一些语句,这些语句可以用于删除加载完成之后SQLLDR我我们创 建的对象:
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

这就可以了。如果取这个日志文件,并在适当的地方插入/,使之成为一个合法的 SQL*Plus脚本,就

万事俱备了;也可能还不行,这取决于当前的权限。例如,假设我登录的模式有 CREATE ANY DIRECTORY权 限,或者能READ访问一个现有的目录,则可能观察到一个错误:
ops$tkyte@ORA10G> INSERT /*+ append */ INTO DEPT
2 (
3 DEPTNO,
4 DNAME,
5 LOC
6 )
7 SELECT
8 "DEPTNO",
9 "DNAME",

10 "LOC"
11 FROM "SYS_SQLLDR_X_EXT_DEPT"
12 /
INSERT /*+ append */ INTO DEPT
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file demo1.log_xt
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 1
初看上去好像不对劲。我作为TKYTE登录到操作系统,我登录的目录是/home/tkyte,而且我拥有这
个目录,所以我当然能写这个目录(毕竟,我在那里创建了SQLLDR日志文件)。为什么会发生错误呢?发 生了什么?现在的情况是,外部表代码在Oracle服务器软件中运行(在我的专业或共享服务器上)。试图 读取输入数据文件的进程是Oracle软件所有者,而不是我的账户。试图创建日志文件的进程也是Oracle 软件所有者,而不是我的账户。显然,Oracle没有必要的权限来写我的目录,因此,对外部表的访问会失 败。这一点很重要。要读一个表,运行数据库的账户(Oracle软件所有者)必须能做下面的事情:
读我们指向的文件。在UNIX中,这说明Oracle 软件所有者必须对指向这个文件的所有目 录路径有读和执行权限。在Windows中,Oracle软件所有者必须能读该文件。
写日志文件目录(即要写日志文件的目录;或者绕过日志文件生成,但是一般来讲,不建 议这样做)。实际上,如果已经存在日志文件,Oracle软件所有者必须能写现有的文件。

写所指定的如何坏文件,就像日志文件一样。 再回到前面的例子,以下命令使得Oracle能写我的目录:
ops$tkyte@ORA10G> host chmod a+rw .
警告 这个命令实际上会使所有人都能写我的目录!这只是一个演示;通常我会使用Oracle软件所有
者自己拥有的一个特殊目录来做这个工作。

接下来,再运行INSERT语句:
ops$tkyte@ORA10G> l

1 INSERT /*+ append */ INTO DEPT
2 (
3 DEPTNO,
4 DNAME,
5 LOC
6 )
7 SELECT
8 "DEPTNO",
9 "DNAME",
10 "LOC"
11* FROM "SYS_SQLLDR_X_EXT_DEPT"
ops$tkyte@ORA10G> /
4 rows created.
ops$tkyte@ORA10G> host ls -l demo1.log_xt
-rw-r--r-- 1 ora10g ora10g 578 Jul 17 10:45 demo1.log_xt
可以看到,这一次访问文件时,我成功地加载了 4行,并创建了日志文件,另外实际上这个日志文件
有“Oracle“拥有,而不属于我的操作系统账户。

15.2.2处理错

理想世界中是没有错误的。输入文件中的数据如果是理想的,就会全部正确地加载。这几乎是不可能 的。那么,如何跟踪加载过程的错误呢?
最常用的方法是使用BADFILE选项。Oracle会在这个坏文件中记录所有未能处理的记录。例如,如 果我们的控制文件包含一个DEPTNO ‘ABC’的记录,这个记录会失败,最后出现在坏文件中,因为’ABC’ 无法转换为一个数字。我们将在下面的例子中展示这一点。
首先,将下面一行添加到demo1.ctl中,作为最后一行(这会向输入添加一行无法加载的数据):

ABC,XYZ,Hello
接下来,运行以下命令,来证明demo1.bad文件尚不存在:
ops$tkyte@ORA10G> host ls -l demo1.bad
ls: demo1.bad: No such file or directory

然后查询外部表来显示内容:
ops$tkyte@ORA10G> select * from SYS_SQLLDR_X_EXT_DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia

现在,我们发现存在这个坏文件,而且可以获取其内容:
ops$tkyte@ORA10G> host ls -l demo1.bad
-rw-r--r-- 1 ora10g ora10g 14 Jul 17 10:53 demo1.bad ops$tkyte@ORA10G> host cat demo1.bad
ABC,XYZ,Hello

但是,如何通过程序来检查这些坏记录以及生成的日志呢?幸运的是,利用另一个外部表就能很容易

地做到。假设我们建立了这个外部表:
ops$tkyte@ORA10G> create table et_bad
2 ( text1 varchar2(4000) ,
3 text2 varchar2(4000) ,
4 text3 varchar2(4000)
5 )
6 organization external
7 (type oracle_loader

8 default directory SYS_SQLLDR_XT_TMPDIR_00000
9 access parameters
10 (
11 records delimited by newline
12 fields
13 missing field values are null
14 ( text1 position(1:4000),
15 text2 position(4001:8000),
16 text3 position(8001:12000)
17 )
18 )
19 location ('demo1.bad')
20 )
21 /
Table created.
这是一个可以读取任何文件而不会遭遇数据类型错误的表(只要文件中的行少于12,000个字符)。如
果行多于12,000个字符,可以再增加更多的文本列来容纳它们。
可以通过一个简单的查询看到被拒绝的记录:
ops$tkyte@ORA10G> select * from et_bad;
TEXT1 TEXT2 TEXT3
--------------- --------------- --------------- ABC,XYZ,Hello

COUNT(*)会 告诉我们有多少记录被拒绝。根据与这个外部表相关的日志文件,可以创建另一个外部
表,它能告诉我们为什么这些记录被拒绝。不过,我们想更进一步,使得这成 为一个可重复的过程。原因 是:如果使用外部表时没有出现错误,坏文件不会“置空“。所以,如果有一些现有的坏文件,其中包含 一些数据,尽管这一次使用外部 表时没有生成任何错误,但是看到坏文件中原有的数据,我们可能会被误 导,误以为外部表中存在错误。
我过去采用过3种方法来解决这个问题:
使用UTL_FILE,并重置坏文件,实际上,就是以写模式打开坏文件,再将其关闭,这样就 能清除坏文件中原有的数据。
使用UTL_FILE重命名现有的坏文件,保留其内容,同时允许创建一个新的坏文件。
在坏文件(和日志文件)名中加入PID。我们将在后面的“多用户问题“一节中介绍这个方
法。
采用这些方法,我们就能区别坏文件中的坏记录是我们刚才生成的,还是这个文件本身以前版本中留

下来的(这对我们来说没有意义)。
ALTER TABLE T PROJECT COLUMN REFERENCED|ALL
这一节前面的COUNT(*)使我想到Oracle 10g中的一个新特性:只访问外部文件中在查询中引用的字
段来优化外部表访问。也就是说,如果外部表定义为有100个数字字段,但是你只选择了其中一个字段, 可以指示Oracle绕过其他99个串转换为数字的过程。听上去很不错,但是可能导致从每个查询返回不同 数目的行。假设外部表中有100行数据。C1列的所有行都是“合法“的,都可以转换为一个数字。而C2 列中的所有数据都不是”合法“的,它们都不能转换为一个数字。如果从这个外部表选择C1,会返回100 行。但是如果从这个外部表选择C2,则会得到0行。
必须显式地启用这个优化,而且你要考虑这样使用是否“安全“(只有对你的应用及其处理有足够的 了解,才能回答这个”是否安全?“的问题)。还是前面的例子,增加一行坏数据,可以想见,查询外部表 时会看到以下输出:
ops$tkyte@ORA10G> select dname
2 from SYS_SQLLDR_X_EXT_DEPT
3 / DNAME
-------------- Sales Accounting Consulting Finance
ops$tkyte@ORA10G> select deptno

2 from SYS_SQLLDR_X_EXT_DEPT
3 / DEPTNO
----------
10
20
30
40
我们知道,“坏”记录已经记入BADFILE。但是,如果只是 ALTER外部表,并告诉Oracle只“投影”
(处理)所引用的列,如下:
ops$tkyte@ORA10G> alter table SYS_SQLLDR_X_EXT_DEPT
2 project column referenced
3 /
Table altered.
ops$tkyte@ORA10G> select dname
2 from SYS_SQLLDR_X_EXT_DEPT
3 / DNAME
-------------- Sales Accounting Consulting Finance

XYZ
ops$tkyte@ORA10G> select deptno
2 from SYS_SQLLDR_X_EXT_DEPT
3 / DEPTNO
----------
10
20
30
40
从各个查询会得到不同数目的行。输入文件中每一行记录的DNAME字段都是合法的,但是DEPTNO列则不然 。 如果没有获取DEPTNO列,就不会拒绝DEPTNO列非法的这个坏记录,所以结果集有显著改变。

15.2.3使用外部表加载不同的文件

通常需要在一段时期内使用一个外部表从不同名的文件加载数据。也就是说,我们现在必须加载 file1.dat,下一周再加载file2.dat,等等。到目前为止,我们一直只是从一个固定的文件名(demo1.dat) 加载。如果随后需要从另一个文件(demo2.dat)加载会怎么样呢?
幸运的是,这很容易满足。可以用ALTER TABLE命令重新指示外部表的位置设置:
ops$tkyte@ORA10G> alter table SYS_SQLLDR_X_EXT_DEPT

2 location( 'demo2.dat' ); Table altered.
仅此而已。对该外部表的下一个查询就会访问文件demo2.dat。

15.2.4多用户问题

在这一节的引言中,我指出过去3种情况下外部表可能没有SQLLDR那么有用。其中之一就是一种特 定的多用户问题。我们刚才看到了如果改变一个外部表的位置,如果使之读取文件2 而不是文件1,等等。 如果多个用户都试图并发地使用这个外部表,而在各个会话中将其指向不同的文件,就会出现这个多用户 问题。
这是不允许的。在任何给定的时刻,外部表会指向一个文件(或一组文件)。如果我登录后,将表修 改为指向文件1,而你几乎同时做了同样的事情,如何我们都查询这个表,就会访问同一个文件。
一般都不会遇到这个问题。外部表不是用来取代“数据库表“;它们只是一种加载数据的方法,你不 能过分倚重外部表,把它们作为你的应用的一部分频繁使用。外部表通常只是DBA或开发人员用来加载信 息的一个工具,可以一次性地加载,或者按周期复发加载(类似于数据仓库加载)。如果DBA要使用同一个 外部表向数据库中加载10个文件,就不应该顺序地加载它们,也就是说,将外部文件指向文件 1,并处理 , 再指向文件2,并处理,如此继续。而应该将外部表指向这两个文件,让数据库来处理:
ops$tkyte@ORA10G> alter table SYS_SQLLDR_X_EXT_DEPT
2 location( 'file1.dat', 'file2.dat')
3 /
Table altered.

如果需要“并行处理“,这是完全可以做到的,数据库已经有相应的内置功能,这在上一章已经介绍
过。
所以,多用户问题只可能是:两个会话试图几乎同时修改文件位置。不过这只是一种需要当心的可能
情况,而我不认为你真的会经常遇到这种情况。
另一个多用户问题与坏文件名和日志文件名有关。如果有多个会话在并发地查看同一个外部表,或者 在使用并行处理(从某种程度上讲是一个多用户情况),会怎么样呢?如果能按会话聚集这些文件,那该多 好,幸运的是,确实可以这样做。可以在文件名中结合以下特殊串:
%P:PID。
%a:并行执行服务器代理ID。为并行执行服务器指定了数字001、002、003等。 采用这种方式,每个会话都会生成自己的坏文件和日志文件。例如,如果你在先前的CREATE TABLE
命令中使用以下BADFILE语法:
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1_%p.bad' LOGFILE 'demo1.log_xt'
就会看到如下命名的一个文件:
$ ls *.bad
demo1_7108.bad

不过,如果时间长了,还是可能遇到问题。大多数操作系统上都会重用 PID。所以前面所列的处理错
误的技术还是很有用,你要重置你的坏文件;或者如果已经存在坏文件,而且你认为这会带来问题,则要 对它重命名。

15.2.5外部表小结

在这一节中,我们分析了外部表。这是Oracle9i及以后版本中的一个新特性,从很大程度上讲,它 可以取代SQLLDR。我们分析了使用外部表的最快捷的方法:使用SQLLDR来转换以前使用的控制文件。这 里还展示了通过坏文件检测和处理错误的一些技术,最后,我们讨论了有关外部表的一些多用户问题。
下面进入这一章最后一节,我们将讨论如何从数据库卸载数据。