当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

ORACLE外部表学习笔记

外部表的含义:

外部表是指不在数据库中的表,如操作系统上的一个按一定格式分割的文本文件或者由ORACLE自己生成的外部表文件。外部表的数据不存储在数据库中,数据库中只记录外部表的元数据。

本文主要针对以下两种驱动方式:

1、ORACLE_DATAPUMP驱动方式

2、ORACLE_LOADER驱动方式

以下分别演示这两种驱动方式的实验过程:

ORACLE_DATAPUMP驱动方式

在外部表的两种驱动方式中,只有ORACLE_DATAPUMP驱动方式可以实现数据的加载和卸载,在一定程度上也可以实现数据的迁移,这种驱动方式加载的数据必须由ORACLE_DATAPUMP卸载生成。下面演示下如何使用ORACLE_DATAPUMP驱动方式将windows 11.2.0.1.0版本数据库里面的包含BLOB类型的表迁移到linux 11.2.0.3.0版本的数据库中,需要注意,使用外部表迁移数据,只能从低版本迁移到高版本。

创建包含BLOB字段的测试表(ORACLE_DATAPUMP驱动方式支持BLOB类型),并用SQLLDR加载部分测试数据。

数据库版本如下:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

创建测试表:

SQL> create table t_images(id number,jpg_path varchar2(50),image blob);

表已创建。

加载数据:
SQLLDR控制文件t_images.ctl内容如下:

LOAD DATA
INFILE 't_images.txt'
INTO TABLE T_IMAGES
TRUNCATE
FIELDS TERMINATED BY ','
(ID CHAR(255),
JPG_PATH CHAR(255),
IMAGE LOBFILE(JPG_PATH) TERMINATED BY EOF)

SQLLDR数据文件t_images.txt内容如下:

1,E:图片婚纱相册181.jpg
2,E:图片婚纱相册182.jpg
3,E:图片婚纱相册183.jpg
4,E:图片婚纱相册184.jpg
5,E:图片婚纱相册185.jpg
6,E:图片婚纱相册186.jpg
7,E:图片婚纱相册187.jpg
8,E:图片婚纱相册188.jpg
9,E:图片婚纱相册189.jpg
10,E:图片婚纱相册1810.jpg

加载数据部分日志如下:

D:load>sqlldr stream/stream control=t_images.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 1月 23 18:17:23 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
达到提交点 - 逻辑记录计数 9
达到提交点 - 逻辑记录计数 10

查询/验证测试数据:

00016

 

测试数据已经准备好了,下面使用ORACLE_DATAPUMP驱动方式卸载测试数据,在卸载之前,需要创建DIRECTORY。

SQL> create or replace directory d_test as 'd:load';

目录已创建。

使用ORACLE_DATAPUMP驱动方式卸载测试数据:

SQL> create table t_images_unload
2  organization external
3  (type oracle_datapump
4  default directory d_test
5  location('t_images.dmp'))
6  as select * from t_images;

表已创建。

在d:load文件夹下就会生成t_images.dmp文件,这就是ORACLE_DATAPUMP驱动方式卸载出的测试数据。将t_images.dmp文件上传到linux服务器,演示下如何使用ORACLE_DATAPUMP驱动方式加载这个数据。

SQL> select * from v$version;

BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建DIRECTORY:

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

目录已创建。

需要注意的是字符集问题,t_images.dmp文件是在字符集为ZHS16GBK的数据库卸载出来的,那么加载的数据库如果字符集不是ZHS16GBK,就可能会遇到中文乱码的问题,下面分别测试字符集为WE8MSWIN1252和ZHS16GBK两种情况。

WE8MSWIN1252

    使用ORACLE_DATAPUMP驱动方式加载t_images.dmp文件。

SQL> CREATE TABLE T_IMAGE_LOAD
2     (id number,jpg_path varchar2(50),image blob)
3     ORGANIZATION EXTERNAL
4      (TYPE ORACLE_DATAPUMP
5        DEFAULT DIRECTORY "IMP"
6        LOCATION
7         ('T_IMAGES.DMP'));

Table created.

    由于字符集不支持中文的问题,查询时中文乱码。

00017

 

ZHS16GBK

    由于字符集一致,不会出现乱码的情况。

00018

 

ORACLE_LOADER驱动方式

ORACLE_LOADER驱动方式可以直接读取一定格式的文本文件,本实验创建ORACLE_LOADER驱动方式的外部表的数据文件采用数据库的告警日志第一部分,内容如下:

alert_dbdream.txt文件内容如下:

OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:40 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:50 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:55:00 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项

在windows 11.2.0.1.0版本,字符集为ZHS16GBK的数据库中创建ORACLE_LOADER驱动方式的外部表。

SQL> create table alert_dbdream ( text varchar2(400) )
2    organization external (
3    type oracle_loader
4    default directory IMP
5    access parameters (
6    records delimited by newline characterset ZHS16GBK
7    nobadfile
8    nodiscardfile
9    nologfile
10    )
11    location('alert_dbdream.txt')
12    )
13    reject limit unlimited
14  ;

表已创建。

查询数据,没有问题。

SQL> select * from alert_dbdream;

TEXT
--------------------------------------------------------------------------------
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:40 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:50 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:55:00 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项

已选择15行。

将alert_dbdream.txt文件另存为UTF8编码,再次查询,中文就会乱码。

00019

SQL> select * from alert_dbdream;

TEXT
--------------------------------------------------------------------------------
锘縊ER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」
O/S-Error: (OS 1) 鍑芥暟涓嶆纭€?!
Fri Feb 22 17:54:40 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」
O/S-Error: (OS 1) 鍑芥暟涓嶆纭€?!
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」
O/S-Error: (OS 1) 鍑芥暟涓嶆纭€?!
Fri Feb 22 17:54:50 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」
O/S-Error: (OS 1) 鍑芥暟涓嶆纭€?!
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」
O/S-Error: (OS 1) 鍑芥暟涓嶆纭€?!
Fri Feb 22 17:55:00 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 鎸囧畾浜嗛潪娉曢€夐」

已选择15行。

将外部表字符集改为UTF8后,乱码问题得到解决。

SQL> alter table alert_dbdream
2  access parameters (
3  records delimited by newline characterset UTF8
4  nobadfile
5  nodiscardfile
6  nologfile
7  );

表已更改。
-----------------------------------------------------------
SQL> select * from alert_dbdream;

TEXT
--------------------------------------------------------------------------------
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:40 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:54:50 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项
O/S-Error: (OS 1) 函数不正确。 !
Fri Feb 22 17:55:00 2013
OER 7451 in Load Indicator : Error Code = OSD-04500: 指定了非法选项

已选择15行。

可见,由于外部表读取的是数据库外面的文件,外部表的字符集并不一定要和数据库的字符集一致,而是和文件编码必须一致。

利用SQLLDR创建外部表

ORACLE_LOADER方式的外部表可以使用SQLLDR创建,在SQLLDR帮助文档中可以看到SQLLDR有external_table参数,该参数就是用于创建外部表。

D:>sqlldr
SQL*Loader: Release 11.2.0.1.0 - Production on 星期一 3月 4 16:23:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
userid -- ORACLE 用户名/口令
control -- 控制文件名
log -- 日志文件名
bad -- 错误文件名
data -- 数据文件名
discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)
skip -- 要跳过的逻辑记录的数目  (默认 0)
load -- 要加载的逻辑记录的数目  (全部默认)
errors -- 允许的错误的数目         (默认 50)
rows -- 常规路径绑定数组中或直接路径保存数据间的行数
(默认: 常规路径 64, 所有直接路径)
bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)
silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
direct -- 使用直接路径                     (默认 FALSE)
parfile -- 参数文件: 包含参数说明的文件的名称
parallel -- 执行并行加载                    (默认 FALSE)
file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)
commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)
readsize -- 读取缓冲区的大小               (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
multithreading -- 在直接路径中使用多线程
resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)
PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'。位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

该参数有3个值:

NOT_USED:不实用外部表加载,默认值。

GENERATE_ONLY:只生成创建外部表和处理数据等相关的SQL,并不加载数据。

EXECUTE:创建外部表,并加载数据。

本实验主要演示GENERATE_ONLY方式,SQLLDR控制文件内容如下:

SQLLDR控制文件alert_dbdream_1.ctl内容如下:

LOAD DATA
INFILE 'alert_dbdream_1.txt'
INTO table alert_dbdream_1
TRUNCATE
FIELDS TERMINATED BY ','
(test CHAR(255))

SQLLDR数据文件内容如下:

SQLLDR数据文件alert_dbdream_1.txt内容如下:

1,宋春风,云和恩墨,北京
2,张耐,云和恩墨,上海
3,闫志兵,云和恩墨,太原
4,李建亮,云和恩墨,杭州
5,尚晓雁,云和恩墨,北京
6,赵翔,云和恩墨,昆明
7,鞠洋,云和恩墨,北京
8,王海舟,云和恩墨,北京
9,董禹,云和恩墨,北京
10,刘洋,云和恩墨,北京

创建测试表alert_dbdream_2

SQL> create table alert_dbdream_2(id  number,name char(10),unit char(20));

表已创建。

使用SQLLDR的external_table参数的GENERATE_ONLY选项生成创建外部表SQL。

D:>sqlldr stream/stream control=alert_dbdream_1.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 11.2.0.1.0 - Production on 星期二 3月 5 11:09:48 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

这样在D盘下会生成alert_dbdream_1.log的文件,创建外部表的SQL就在这个文件内,以下是这个文件的内容。

SQL*Loader: Release 11.2.0.1.0 - Production on 星期二 3月 5 11:09:48 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
控制文件:      alert_dbdream_1.ctl
数据文件:      alert_dbdream_1.txt
错误文件:    alert_dbdream_1.bad
废弃文件:    未作指定
(可废弃所有记录)
要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续:    未作指定
所用路径:       外部表
表 ALERT_DBDREAM_2,已加载从每个逻辑记录
插入选项对此表 TRUNCATE 生效
列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,       CHARACTER
NAME                                 NEXT    10   ,       CHARACTER
UNIT                                 NEXT    20   ,       CHARACTER
文件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'D:'

用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_ALERT_DBDREAM"
(
"ID" NUMBER,
"NAME" CHAR(10),
"UNIT" CHAR(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'alert_dbdream_1.bad'
LOGFILE 'alert_dbdream_1.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"NAME" CHAR(10)
TERMINATED BY ",",
"UNIT" CHAR(20)
TERMINATED BY ","
)
)
location
(
'alert_dbdream_1.txt'
)
)REJECT LIMIT UNLIMITED

用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO ALERT_DBDREAM_2
(
ID,
NAME,
UNIT
)
SELECT
"ID",
"NAME",
"UNIT"
FROM "SYS_SQLLDR_X_EXT_ALERT_DBDREAM"

用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_ALERT_DBDREAM"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
从 星期二 3月  05 11:09:48 2013 开始运行
在 星期二 3月  05 11:09:48 2013 处运行结束
经过时间为: 00: 00: 00.11
CPU 时间为: 00: 00: 00.00

一般情况下,只需要将该日志中的SQL语句复制-粘贴-运行就可以创建外部表,并通过外部表的方式将数据加载到数据库内。下面就上面的日志,简要分析下创建外部表的SQL。

CREATE TABLE "SYS_SQLLDR_X_EXT_ALERT_DBDREAM"
(
"ID" NUMBER,
"NAME" CHAR(10),
"UNIT" CHAR(20)
)
-------------------------------------------------
--第一部分声明表的结构
-------------------------------------------------
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
-------------------------------------------------
--第二部分声明外部表的驱动方式和DIRECTORY
-------------------------------------------------
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'alert_dbdream_1.bad'
LOGFILE 'alert_dbdream_1.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"NAME" CHAR(10)
TERMINATED BY ",",
"UNIT" CHAR(20)
TERMINATED BY ","
)
)
-------------------------------------------------
--第三部分声明外部表的加载参数
-------------------------------------------------
location
(
'alert_dbdream_1.txt'
)
)REJECT LIMIT UNLIMITED
-------------------------------------------------
--第四部分声明数据来源
-------------------------------------------------

修改外部表属性

从上文创建外部表的SQL可以看到,外部表的属性分为4大部分,那么修改外部表的属性也分为这4个部分。

  1. 修改外部表的结构

外部表的结构和其他表一样,可以直接修改,但是修改后ACCESS PARAMETERS里面定义的表结构信息通常也需要修改(修改字段长度,可能不需要修改ACCESS PARAMETERS里面定义的表结构信息),拿上文创建外部表的SQL为例。

SQL> select * from "SYS_SQLLDR_X_EXT_ALERT_DBDREAM";

ID NAME       UNIT
---------- ---------- --------------------
1 宋春风     云和恩墨
2 张耐       云和恩墨
3 闫志兵     云和恩墨
4 李建亮     云和恩墨
5 尚晓雁     云和恩墨
6 赵翔       云和恩墨
7 鞠洋       云和恩墨
8 王海舟     云和恩墨
9 董禹       云和恩墨
10 刘洋       云和恩墨

已选择10行。

修改该外部表的结构,增加ADDR字段。

SQL> alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM add addr char(10);

表已更改。

由于只改了表结构,ACCESS PARAMETERS子句中的表结构信息没有更改,查询报ORA-29913、ORA-29400错误。

SQL> select * from "SYS_SQLLDR_X_EXT_ALERT_DBDREAM";
select * from "SYS_SQLLDR_X_EXT_ALERT_DBDREAM"
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-04043: table column not found in external source:
ADDR

修改ACCESS PARAMETERS子句的表结构信息,和上文表结构一致。

SQL> alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM
2  ACCESS PARAMETERS
3  (
4    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
5    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'alert_dbdream_1.bad'
6    LOGFILE 'alert_dbdream_1.log_xt'
7    READSIZE 1048576
8    FIELDS TERMINATED BY "," LDRTRIM
9    REJECT ROWS WITH ALL NULL FIELDS
10    (
11      "ID" CHAR(255)
12        TERMINATED BY ",",
13      "NAME" CHAR(10)
14        TERMINATED BY ",",
15      "UNIT" CHAR(20
16          TERMINATED BY ",",
17      ADDR CHAR(10)
18       TERMINATED BY ","
19  )
20  );

表已更改。

修改后,可以正常查询。

SQL> select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM;

ID NAME       UNIT                 ADDR
---------- ---------- -------------------- ----------
1 宋春风     云和恩墨             北京
2 张耐       云和恩墨             上海
3 闫志兵     云和恩墨             太原
4 李建亮     云和恩墨             杭州
5 尚晓雁     云和恩墨             北京
6 赵翔       云和恩墨             昆明
7 鞠洋       云和恩墨             北京
8 王海舟     云和恩墨             北京
9 董禹       云和恩墨             北京
10 刘洋       云和恩墨             北京

已选择10行。

需要注意的是,新增的字段的数据要在SQLLDR的数据文件中存在,否则会报错,如下:

SQL>  alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM add test char(10)

2  ACCESS PARAMETERS
3  (
4    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
5    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'alert_dbdream_1.bad'
6    LOGFILE 'alert_dbdream_1.log_xt'
7    READSIZE 1048576
8    FIELDS TERMINATED BY "," LDRTRIM
9    REJECT ROWS WITH ALL NULL FIELDS
10    (
11      "ID" CHAR(255)
12        TERMINATED BY ",",
13      "NAME" CHAR(10)
14        TERMINATED BY ",",
15      "UNIT" CHAR(20)
16          TERMINATED BY ",",
17      ADDR CHAR(10)
18       TERMINATED BY ","
19      test char(10)
20       TERMINATED BY ","
21  )
22  );
表已更改。
SQL> select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM;
select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access
parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma,
date_format, defaultif, enclosed, ltrim, lrtrim, ldrtrim, notrim, nullif,
optionally, ), rtrim"
KUP-01008: the bad identifier was: test
KUP-01007: at line 16 column 5
  1. 修改外部表的驱动

外部表的访问驱动不可更改(在ORACLE官方文档中没有对外部表访问驱动修改部分),但是可以修改DIRECTORY。

SQL> select * from dba_directories where length(DIRECTORY_PATH)=3;

OWNER      DIRECTORY_NAME                 DIRECTORY_PATH
---------- ------------------------------ ------------------------
SYS        IMP                            d:
SYS        SYS_SQLLDR_XT_TMPDIR_00000     D:

将外部表的DIRECTORY由SYS_SQLLDR_XT_TMPDIR_00000改为IMP。

SQL> alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM default directory IMP;

表已更改。
SQL> select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM;

ID NAME       UNIT                 ADDR
---------- ---------- -------------------- ----------
1 宋春风     云和恩墨             北京
2 张耐       云和恩墨             上海
3 闫志兵     云和恩墨             太原
4 李建亮     云和恩墨             杭州
5 尚晓雁     云和恩墨             北京
6 赵翔       云和恩墨             昆明
7 鞠洋       云和恩墨             北京
8 王海舟     云和恩墨             北京
9 董禹       云和恩墨             北京
10 刘洋       云和恩墨             北京

已选择10行。
  1. 修改外部表的加载参数

ORACLE外部表加载参数ACCESS PARAMETERS部分无法单独修改,如果要修改ACCESS PARAMETERS子句中的参数,就需要将这一大串参数全部指定,如下将外部表字符集改为UTF8:

SQL> alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM
2  ACCESS PARAMETERS
3  (
4  RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
5   BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'alert_dbdream_1.bad'
6   LOGFILE 'alert_dbdream_1.log_xt'
7   READSIZE 1048576
8   FIELDS TERMINATED BY "," LDRTRIM
9   REJECT ROWS WITH ALL NULL FIELDS
10   (
11   "ID" CHAR(255)
12   TERMINATED BY ",",
13   "NAME" CHAR(10)
14   TERMINATED BY ",",
15   "UNIT" CHAR(20)
16    TERMINATED BY ",",
17   ADDR CHAR(10)
18   TERMINATED BY ","
19   )
20   );

表已更改。

由于外部表的字符集改为UTF8后,和SQLLDR数据文件的字符编码不一致,查询外部表中文就会乱码。

SQL> select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM;

ID NAME       UNIT                 ADDR
---------- ---------- -------------------- ----------
2 ??       ????ī              ???
3 ????      ????ī              ??
4 ????      ????ī              ???
5 ???     ????ī              ????
6 ??       ????ī              ???
7 ???       ????ī              ????
8 ????     ????ī              ????
9 ???       ????ī              ????
10 ???       ????ī              ????

已选择9行。
  1. 修改外部表的数据来源

该部分可以直接修改,但是此部分指定的文件必须存在,并且数据格式要和外部表结构对应,如下实验:

将数据文件alert_dbdream_1.txt改名为alert_dbdream_2.txt。

D:>move alert_dbdream_1.txt alert_dbdream_2.txt

移动了         1 个文件。

修改外部表的数据来源为alert_dbdream_2.txt。

SQL> alter table SYS_SQLLDR_X_EXT_ALERT_DBDREAM location ('alert_dbdream_2.txt');

表已更改。
SQL> select * from SYS_SQLLDR_X_EXT_ALERT_DBDREAM;

ID NAME       UNIT                 ADDR
---------- ---------- -------------------- ----------
1 宋春风     云和恩墨             北京
2 张耐       云和恩墨             上海
3 闫志兵     云和恩墨             太原
4 李建亮     云和恩墨             杭州
5 尚晓雁     云和恩墨             北京
6 赵翔       云和恩墨             昆明
7 鞠洋       云和恩墨             北京
8 王海舟     云和恩墨             北京
9 董禹       云和恩墨             北京
10 刘洋       云和恩墨             北京

已选择10行。

但是如果文件不存在或格式不一致,查询外部表时都会报错。

本文固定链接: http://www.dbdream.com.cn/2013/03/oracle%e5%a4%96%e9%83%a8%e8%a1%a8%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2013年03月12日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORACLE外部表学习笔记 | 信春哥,系统稳,闭眼上线不回滚!
关键字:

ORACLE外部表学习笔记:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter