开发者社区> 东方瀚海鲍> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PLSQL_Oracle外部表的概念和使用(案例)

简介: 2014-08-25 Created By BaoXinjian 一、摘要 ORACLE外部表用来存取数据库以外的文本文件(Text File)或ORACLE专属格式文件。 因此,建立外部表时不会产生段、区、数据块等存储结构,只有与表相关的定义放在数据字典中。
+关注继续查看

2014-08-25 Created By BaoXinjian

一、摘要


ORACLE外部表用来存取数据库以外的文本文件(Text File)或ORACLE专属格式文件。

因此,建立外部表时不会产生段、区、数据块等存储结构,只有与表相关的定义放在数据字典中。

外部表,顾名思义,存储在数据库外面的表。

当存取时才能从ORACLE专属格式文件中取得数据,外部表仅供查询,不能对外部表的内容进行修改(INSERT、UPDATE、DELETE操作)。

不能对外部表建立索引。因为创建索引就意味着要存在对应的索引记录。而外部表其实在没有存储在数据库中。

故在外部是无法建立索引的,如果硬要建立的话,则系统会提示“操作在外部组织表上不受支持”的错误提示。

 

1. 外部表特征

  • 位于文件系统之中(一定要在数据库服务器中,而不是其它网络路径),按一定格式分割, 例如@#$等,文本文件或者其他类型的文件可以作为外部表。
  • 对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。
  • 外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。
  • ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。
  • 可以查询操作和连接。也可以并行操作。
  • 数据在数据库的外部组织,是操作系统文件。
  • 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

2. 外部表限制

  • 只能对表进行SELECT,不能进行DELETE、UPDATE、INSERT这些DML操作。
  • 因为外部表需要在ORACLE数据库“服务端”创建目录,OS文件必须放在这些目录中。即这些文件只能放在数据库服务端。如果数据文件不位于服务器,则无法使用外部表。
  • 外部表上不能创建索引。但可以建立视图。
  • 外部表不支持LOB对象。如果要使用LOB类型,则不能使用外部表。

3. 外部表优势

如果要谈外部表的优势,一般会和SQLLDR来对比,外部表很多语法跟SQLLDR控制文件确实有很多类似的地方,下面谈谈自己的理解和"Oracel 9i&10g编程艺术"里面的一些对比

  • SQLLDR需要将数据装载入库后才能查询相关记录,如果只是为了查询一些记录,外部表确实比SQLLDR要有优势一些,很方便又不占用数据库存储空间。 尤其是很大的数据,以前做移动综合分析项目处理SGSN话单(几百G的数据,如果全部装载入库,非常浪费空间和时间)时就有这样的体会。外部表虚拟的导入 过程极快
  • 当平面文件改变时,外部表内的数据会跟着改变。这样避免了插入、更新、删除等操作,对于超大记录的外部表相当有优势,我只需要使用Shell命令就能搞定数据库需要很高代价才能完成的事情。
  • 外部表可以使用复杂的WHERE 条件有选择地加载数据。尽管SQLLDR 有一个WHEN 子句用来选择要加载的行,但是你只能使用AND 表达式和执行相等性比较的表达式,在WHEN 子句中不能使用区间(大于、小于),没有OR 表达式,也没有IS NULL 等。
  • 能够合并(MERGE)数据。可以取一个填满数据的操作系统文件,并由它更新现有的数据库记录。
  • 能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分。
  • 使用INSERT 更容易地执行多表插入。从Oracle9i 开始,通过使用复杂的WHEN 条件,可以用一个INSERT 语句插入一个或多个表。尽管SQLLDR 也可以加载到多个表中,但是相应的语法相当复杂。

4. 两种数据源方式

  • 使用Oracle Lorder产生的数据文件
  • 使用Exp/Expdp产生的dump文件

 

二、案例 - 创建外部表,并进行增删改查操作


案例:创建外部表,并导入测试数据,进行测试

1. 创建目录

CREATE DIRECTORY extertable as '/home/oracle/extertable';

 

 

2. 创建测试的数据文件,并测试

 

3. 创建外部表

CREATE TABLE exter_test_table
(
   ID      NUMBER  (5),
   NAME    VARCHAR (12),
   SEX     VARCHAR (8),
   AGE     NUMBER  (3),
   GRADE   NUMBER  (5)
)
ORGANIZATION EXTERNAL
   ( 
     TYPE oracle_loader 
     DEFAULT DIRECTORY extertable 
     ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ) 
     LOCATION ('student.data' )
   );

ALTER TABLE exter_test_table REJECT LIMIT UNLIMITED;

 

4. 创建表后,可直接查询出在数据文件中的资料

 

 

5. 外部表只能进行select,无法进行delete / update, 如需进行DML操作只能在数据文件本身中

 

6. 进行操作时,会在数据文件目录中产生log文件,记录数据导入和导出的情况

 

7. 查询数据库中外部表的情况 dba_external_locations / dba_external_tables

 

 

三、案例 - 删除外部表


删除外部表SQL语法跟普通表一样,但是不同之处在于有可能还要删除与之对应的目录对象。

当外部表不用时,需要及时删除外部表或者与之对应的目录对象。

不过在删除这些内容时会有一些限制。这些限制主要是管理上的限制,而不是技术上的限制。

也就是说,Oracle数据库系统没有对其进行强制的限制。

但是如果 数据库管理员不遵守这些限制的话,可能会出现一些问题。

如要先删除外部表,然后再删除目录对象。有时候一个目录对象中可能会包含多个外部表。

此时必须要确 认所有的外部表都不用了,都已经删除干净了,然后才能够删除目录对象。

在创建外部表时,操作系统会判断一下,与之对应的目录对象是否已经创建。

但是在删除 对象时,系统不会去判断跟这个目录对象关联的外部表是否已经全部删除。

如果目录对象删除了,但是还有外部表存在。

此时查询这个外部表的时候,系统就会提示 “对象不存在”的错误信息。所以这个删除目录对象时,数据库系统缺乏一种检查,此 时只有数据库管理员在删除目录对象时,先手工确认一下这个目录对象是否存在其他的外部表

 

Thanks and Regards

参考:潇湘隐者大神 http://www.cnblogs.com/kerrycode/p/3894260.html

ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
oracle查询表 索引、唯一约束、字段
oracle查询表 索引、唯一约束、字段
37 0
oracle字符集转换工具DMU使用
关于oracle字符集的介绍参考:http://blog.csdn.net/cymm_liu/article/details/7605081 (此博客也包含了一种修改字符集的方法,但是不建议使用) 今天我们为了解决这个问题:http://blog.csdn.net/cymm_liu/article/details/12013243 采用oracle 的字符集转换工具DMU来进行操作。
1249 0
oracle通过job定时执行任务
2013年8月27日 星期二 20:49 在项目中,经常会遇到需要定时完成的任务,比如定时更新数据,定义统计数据生成报表等等,其实这些事情都可以使用Oracle的Job来完成。下面考试大就结合我们实验室项目实际,简单介绍一下在Oracle数据库中通过Job完成自动创建表的方法。   整个过程总共分为两步。虽然整个过程都非常简单,但是对于初学Oracle的生手还是有很多地方需要注意的。
1764 0
518
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载