oracle 外部表(DOC可编).docVIP

-- Oracle外部表 --================= 外部表只能在Oracle 9i之后来使用。简单地说，外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，我们 可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。 一、外部表的特性 位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。 对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。 外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。 ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。 二、创建外部表的注意事项 1.需要先建立目录对象 2.对于操作系统文件的要求 文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件 3.在建立临时表时的相关限制 对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。 对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。 建议不用使用特殊的列标题字符 在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。 创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。 简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。而没有存储实际的数据。 由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。 4.删除外部表或者目录对象 一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象。 如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到对象不存在的错误信息。 查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。 5.对于操作系统平台的限制 不同的操作系统对于外部表有不同的解释和显示方式 如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。 建议避免不同操作系统以及不同字符集所带来的影响 三、创建外部表 使用CREATE TABLE语句的ORGANIZATION EXTENERAL子句来创建外部表。外部表不分配任何盘区，因为仅仅是在数据字典中创建元数据。 1.外部表的创建语法 createtabletable_name (col1 datatype1,col2 datatype2,col3 datatype3) organization exteneral (.....) 2.由查询结果集，使用Oracle_datapump来填充数据来生成外部表 a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限 [oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data sys@ORCLcreateorreplacedirectory dat_diras/home/oracle/external_tb/data/; sys@ORCLgrantread,writeondirectory dat_dirtoscott; b.创建外部表 scott@ORCLcreatetableex_tb1 --创建外部表 2 (ename,job,sal,dname) --表列描述，注意未指定数据类型 3 organizationexternal 4 ( 5 typeoracle_datapump --使用datapump将查询结果填充到外部表,注,