ORACLE外部表学习笔记
Mar122013
外部表的含义：
外部表是指不在数据库中的表，如操作系统上的一个按一定格式分割的文本文件或者由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
查询/验证测试数据：
测试数据已经准备好了，下面使用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.
由于字符集不支持中文的问题，查询时中文乱码。
ZHS16GBK：
由于字符集一致，不会出现乱码的情况。
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编码，再次查询，中文就会乱码。
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个部分。
- 修改外部表的结构
外部表的结构和其他表一样，可以直接修改，但是修改后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
- 修改外部表的驱动
外部表的访问驱动不可更改(在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行。
- 修改外部表的加载参数
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行。
- 修改外部表的数据来源
该部分可以直接修改，但是此部分指定的文件必须存在，并且数据格式要和外部表结构对应，如下实验：
将数据文件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行。
但是如果文件不存在或格式不一致，查询外部表时都会报错。
ORACLE外部表学习笔记：等您坐沙发呢！
发表评论
