oracle外部表
- 格式:doc
- 大小:81.50 KB
- 文档页数:8
外部表介绍
ORACLE外部表用来存取数据库以外的文本文件(Text File)或ORACLE专属格式文件。因此,建立外部表时不会产生段、区、数据块等存储结构,只有与表相关的定义放在数据字典中。外部表,顾名思义,存储在数据库外面的表。当存取时才能从ORACLE专属格式文件中取得数据,外部表仅供查询,不能对外部表的内容进行修改(INSERT、UPDATE、DELETE操作)。不能对外部表建立索引。因为创建索引就意味着要存在对应的索引记录。而外部表其实在没有存储在数据库中。故在外部是无法建立索引的。如果硬要建立的话,则系统会提示“操作在外部组织表上不受支持”的错误提示。
Notice: 外部表是ORACLE 9i后引入的。
外部表特征
(1)位于文件系统之中(一定要在数据库服务器中,而不是其它网络路径),按一定格式分割,
例如@#$等,文本文件或者其他类型的文件可以作为外部表。
(2)对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。
(3)外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。
(4) ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。
(5)可以查询操作和连接。也可以并行操作。
(6)数据在数据库的外部组织,是操作系统文件。
(7)操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
外部表范例:
1:创建目录对象并授权
从9i开始,ORACLE数据库若需要存取文件系统,就必须使用目录对象,以相对路径方式存取文件,强化数据库的安全性。建立目录对象、授予权限。
SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab';
给用户授予指定目录的操作权限
SQL>GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;
2:创建外部表
我创建了一个平面文件作为测试用例,如下所示,总共5条记录,其中一条错误记录[oracle@DB-Server exterltab]$ more student.data
10001@#$kerry@#$male@#$28@#$1
10002@#$jimmy@#$male@#$22@#$1
10003@#$ken@#$male@#$21@#$1
10004@#$merry@#$femal@#$20@#$1
this is a bad file
CREATE TABLE EXTER_TEST
(
ID NUMBER(5) ,
NAME VARCHAR(12) ,
SEX VARCHAR(8) ,
AGE NUMBER(3) ,
GRADE NUMBER(1)
) ORGANIZATION EXTERNAL
(
type oracle_loader
default directory dump_dir
access parameters
(
records delimited by newline
fields terminated by'@#$'
)
location ('student.data')
);
外部表语法也是蛮复杂的,参数选项非常多,这里不做过多解释。有兴趣自然可以翻阅官方文档。
SQL> select * from exter_test;
select * from exter_test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
出现下面错误,是因为student.data文件中有不符合规范的记录,可以删除“this is a bad file”这一条记录,但是这是为了测试下面情况,所以可以通过执行 alter table exter_test reject limit unlimited;跳过一些限制。
SQL> alter table exter_test reject limit unlimited;
Table altered.
SQL> select * from exter_test;
ID NAME SEX AGE GRADE
---------- ------------ -------- ---------- ----------
10001 kerry male 28 1
10002 jimmy male 22 1
10003 ken male 21 1
10004 merry femal 20 1
SQL>
此时去查看/oradata/exterltab,你会发现自动生成了EXTER_TEST_8907.bad 和
EXTER_TEST_8907.log两个文件,其中log记录访问外部表的记录信息,bad文件记录错误记录的信息。大体如下所示
[oracle@DB-Server exterltab]$ ls
EXTER_TEST_8907.bad EXTER_TEST_8907.log student.data
[oracle@DB-Server exterltab]$