Oracle外部表

oraclestudyroad

2011-10-10

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

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

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

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

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

[Oracle@linux sqlldr]$ pwd
/u01/sqlldr
[oracle@linux sqlldr]$ cat demo1.ctl
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
ABC,XYZ,Hello
[oracle@linux sqlldr]$ sqlldr ing/ing demo1.ctl external_table=generate_only
SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011
Copyright (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 2011
Copyright (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.14
CPU 时间为: 00: 00: 00.04