ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 外部表 External Table

外部表 External Table

原创 Linux操作系统 作者:ofan2012 时间:2012-02-05 23:34:08 0 删除 编辑
 
 External tables access data in external sources as if it were in a table in the database.
You can connect to the database and create metadata for the external table using DDL.
The DDL for an external table consists of two parts: one part that describes the Oracle
column types, and another part (the access parameters) that describes the mapping of
the external data to the Oracle data columns.
创建的语法类似于: "CREATE TABLE ... ORGANIZATION EXTERNAL"
数据在数据库的外部组织,是操作系统文件。
操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
数据是只读的。(外部表相当于一个只读的虚表)
不可以在上面运行任何 DML 操作,不可以创建索引。
可以查询操作和连接。可以并行操作。
建立外部表的步骤:
1、创建以“,”分隔的文件:
在PL/SQL DEVELOP 里,
select * from emp where deptno=10;
select * from emp where deptno=20;
select * from emp where deptno=30;
用EMP表生成3个部门的数据文件,导出为CSV格式,命名为EMP10.CSV,EMP20.CSV,EMP30.CSV,去掉标题,记得哈...
2、创建一个Directory:
create directory TestTable_diras 'D:/Test' ;
目录问题导致了我折腾了半天多的时间,按照引用的这篇BLOG里的建立情况,应该是没有什么问题,可是不知道自己的操作系统的配置环境有什么问题,我的是,WIN XP ,Oracle9i Enterprise Edition Release 9.2.0.1.0 老是提示以下错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错( a- z% K. Q/ h3 d A' /
ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access parameters
今天早上发现竟然是目录造成的,网上查了很久没有我的问题的解决方案,突然记得之前也出现类似目录情况,因此试了下,把目录改成create or replace directory TestTable_diras 'D:/Test/' ; 多了一"/"后就解决了,应该属于个例....郁闷了半天啊..
3、创建一个外部表:
(这个表可是用了我一天的时间,GOOGLE了N篇帖子,环境不同(数据库编码,字符集,语言)都会造成相应的问题,测试时通过的,我遇到的主要问题是日期和字符长度)
CREATE TABLE EXT_EMP (
emp_id number(4),
ename varchar2(12),
job varchar2(12) ,
mgr_id number(4) ,
hiredate date,
salary number(8),
comm number(8),
dept_id number(2)
)
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_TEST
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
badfile 'bad.txt'
FIELDS TERMINATED BY ','
optionally enclosed BY '"'
missing field VALUES are NULL
(
emp_id,
ename,
job,
mgr_id,
hiredate date "YYYY-MM-DD HH24:MI:SS",
salary,
comm ,
dept_id
)
)
LOCATION('emp10.csv','emp20.csv','emp30.csv')
)REJECT LIMIT UNLIMITED
对上面的SQL中的几个主要部分做个简单的说明:
Ø Create table:这部分代码与标准的表一样,并且在里面指定字段名等内容,没有特别的地方
Ø ORGANIZATION EXTERNAL: 这个子句就表明现在声明的是一个外部表而不是一张普通的表
Ø DEFAULT DIRECTORY temp: 这个子句指定外部表的文件在哪个目录中取得
Ø Type oracle_loader: 数据转换驱动器,oracle_loader为默认,也可以改换其他
Ø Accessparameters: 设置转换参数,例如(fields terminatedby',')表示以','为字段间的分隔符
Ø RECORDS DELIMITED BY NEWLINE: 这个子句说明文本文件中的每一行就是一个记录
Ø FIELDS TERMINATED BY ',': 这个子句用于表示各个字段间用什么来分隔
Ø MISSING FIELD VALUES ARE NULL: 这个子句说明如果一个记录中某个字段的值没有,则按“空”处理
Ø (....hiredate date "YYYY-MM-DD HH24:MI:SS",.....)这个日期格式一定要完整的把表所有字段都列入其中,否则报错, 它用于指定日期型字段的格式码或许其它字段格式,这个格式码将直接与文件中的格式相对应,这样才能实现数据的正确读取和导入
Ø Optionally enclosed BY ' " ' : 数据使用"作为数据格式符号
Ø LOCATION('data.txt'): 这个子句用于指明外部文件的文件名+目录名,就能在操作系统中找到外部文件了
外部表的错误处理
1、REJECT LIMIT子句
在创建外部表时最后加入LIMIT子句,表示可以允许错误的发生个数。
* 默认的REJECT LIMIT值为0
* REJECT LIMIT UNLIMITED则不会报错
2、BADFILE 和 NOBADFILE 子句
在accessparameters中加入BADFILE'BAD_FILE.txt'子句,则所有数据转换错误的值会被放入'BAD_FILE.txt'中
使用NOBADFILE子句则表示忽略转换错误的数据
● 如果不写BADFILE或NOBADFILE,则系统自动在源目录下生成与外部表同名的.BAD文件
● BADFILE只能记录前1次操作的结果,他会被第2次操作所覆盖。
3、LOGFILE 和 NOLOGFILE 子句
在accessparameters中加入LOGFILE'LOG_FILE.log'子句,则所有Oracle的错误信息放入'LOG_FILE.log'中
使用NOLOGFILE子句则表示不记录错误信息到log中
● 如果不写LOGFILE或NOLOGFILE,则系统自动在源目录下生成与外部表同名的.LOG文件
例如:我修改了EMP20.CSV中的一个月份改成15月,查看相应的日志得到如下信息:
error processing column HIREDATE in row 5 for datafile d:/temp/emp20.csv
ORA-01843: not a valid month
同时查看相应的BAD.TXT里可以看到那条错误记录:
"7902","FORD ","ANALYST","7566","1981-15-03","3000.00","","20"
修改外部表语句
外部表与堆表一样可以之用ALTER TABLE命令修改表属性
* REJECT LIMIT --错误数
* DEFAULT DIRECTORY --默认目录
* ACCESS PARAMETERS --参数
* LOCATION --数据文件
* ADD COLUMN --增加列
* MODIFY COLUMN --列定义
* DROP COLUMN --删除列
* RENAME TO --外部表更名
其他约束 (外部表作为sqlldr的替换产品,在使用范围上,还是有一定的局限性质)
● 外部表无法使用insert、update、delete等操作,要修改其数据只能通过修改数据文件。
● 外部表不能建立索引,如要建立,则需要先create table XX as select * from TestTable
● 外部表不支持多用户的并发修改,也不支持LOB类型,而且访问路径中的文件必须在服务器端
查看外部表信息:
SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERS FROM DBA_EXTERNAL_TABLES;
SELECT * FROM DBA_EXTERNAL_LOCATIONS.
最后附上自己的源代码:
test.txt 内容:
abc,abcdef,fefasdf,2011-12-21
abc,abcdef,fefasdf,2011-12-21
abc,abcdef,fefasdf,2011-12-21
abc,abcdef,fefasdf,2011-12-21
abc,abcdef,fefasdf,2011-12-21
代码:
create or replace directory dir_ext_test as 'f:/dir_ext_test/';
create table tb_ext_test
(
a varchar2(200),
b varchar2(200),
c varchar2(200),
d date
)
organization external
(
type oracle_loader
default directory dir_ext_test
access parameters
(
records delimited by newline
fields terminated by ','
MISSING FIELD VALUES ARE NULL
(a,b,c,d date "yyyy-mm-dd" )
)
location ('test.txt')
)
reject limit unlimited;
select *
from tb_ext_test;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26622598/viewspace-715654/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-01-14

  • 博文量
    31
  • 访问量
    127687