oracle外部表不是预期的格式,Oracle之外部表[转]

Oracle外部表允许将数据文件映射为数据库表,提供了一种高效处理大量数据的方法。通过创建Directory,拷贝文件,然后定义外部表,用户可以实现对文件内容的只读查询,就像操作普通表一样。外部表不支持DML操作和索引,但支持查询和连接。在遇到大量数据存储和检索需求时,这一特性提供了灵活的解决方案。
摘要由CSDN通过智能技术生成

有时候,数据库海量数据的存储,是一个令人头疼的问题。别的不说,光是频繁的执行insert(1000次/秒)都是一场恶梦。但是,如果将数据保存到文家里,而不是数据库中,数据序列化的开销就小得多了。但是,对于文件的各种复杂检索,又是一件相当麻烦的事。

幸好,Oracle有这样一种特性,它可以将某些特定格式的文件映射到数据库中,形成一个“表”,称为“外部表”。单用户更改文件内容时,外部表中的数据即随之改变。同时,用户又可以像检索普通表一样,以只读的方式对外部表进行检索。

我们假设有这样一个文件(DATA.TXT):

1|this is a string

2|这里是个字符串

3|ABC

要把这样一个文件映射成外部表,有以下工作要做:

首先,我们需要为Oracle创建一个Directory,

创建方式为,在数据库中执行,须用DBA用户创建,并给应用授权。create directory EXT_TABLE_DIR as

'/home/oracle/app/oracle/oradata/php/'

注意“/home/oracle/app/oracle/oradata/php/”是一个存在于Oracle数据库服务器本身上边的实际存在的文件夹; 然后,将DATA.TXT文件拷贝到上述文件夹下;

最后,创建一个对应外部表,

create table EXT_TABLE_NAME

(

COL_1 NUMBER,

COL_2 VARCHAR2(512)

)

organization external

(

type oracle_loader

default directory EXT_TABLE_DIR

access parameters ( fields terminated by '|' )

location ('DATA.TXT')

)

reject limit unlimited

注意蓝色部分,EXT_TABLE_NAME是要映射成的外部表名称,EXT_TABLE_DIR是第一步里我们创建的Oracle的Directory,“|”是文件里的分割符,DATA.TXT是文件名。

需要补充的是,最后有一句“reject limit

unlimited”,告诉Oracle这个外部表没有行数限制。否则,当文件中的数据量超过200万行时,在对表进行检索时,就会出现ORA-30653,“reject

limit reached”错误。

Oracle 9i 的一项新特性就是 External

Table,它就象通常的数据库表一样,拥有字段和数据类型约束,并且可以查询,但是表中的数据却不存储在数据库中,而是在与数据库相关联的普通外部文件里。当你查询

External Table 时,Oracle 将解析该文件并返回符合条件的数据,就象该数据存储在数据库表中一样。

具体的定义可以参见《 Oracle 概念手册》,以下的几点需要注意:

:外部表的描述:

> 创建的语法类似于: "CREATE TABLE ... ORGANIZATION

EXTERNAL"

> 数据在数据库的外部组织,是操作系统文件。

> 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

> 数据是只读的。(外部表相当于一个只读的虚表)

> 不可以在上面运行任何 DML 操作,不可以创建索引。

> 可以查询操作和连接。可以并行操作。

例子:

假如有如下两个数据文件:

1: 数据文件的格式

F1.TXT文件:

13234,FIRSTS

46464,TESTA

F2.TEXT文件:

13234,SECONDS

46464,TEST

2:创建目录,并用DBA进行授权;

sql> create directory test_dir as 'E:temp';

sql>grant read,write on directory test_dir to

users;

注意:创建完毕逻辑目录之后要把平面文件拷贝到该目录下,另外还要注意文件名字不要写错。

一定要给oracle用户对这个目录可读可写的权限,操作系统层面,如使用chmod -R 777

test_dir;

3:使用被授权的用户users创建外部表:

create table test_table

(ms_no varchar(20),

tip varchar(20),

descs varchar(20))

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY test_dirACCESS

Parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_dev.txt'

LOGFILE 'log_dev.txt'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(ms_no,tip,descs)

)

LOCATION('F1.txt','F2.txt')

)

;

表创建完成.当然也可以导入一个文件

4:进行SELECT 操作看是否正确;

SQL>select * from test_table

结果如下:

MS_NO TIP DESCS

-------------------- --------------------

--------------------

13234 FIRSTS

46464 TESTA

13234 SECONDS

46464 TEST

: 如何得到外部表的有关信息:

SQL> DESC DBA_EXTERNAL_TABLES;

Name Type Nullable

----------------------- ------------- - ----

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TYPE_OWNER CHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNER CHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMIT VARCHAR2(40) Y

ACCESS_TYPE VARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECT

OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相关信息;

:如何得到外部路径的信息:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到该表结构:

Name Type Nullable

--------------- -------------- --------

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

LOCATION VARCHAR2(4000) Y

DIRECTORY_OWNER CHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * from DBA_EXTERNAL_LOCATIONS;

得到具体信息;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值