ORACLE 外部表的简单使用

外部表概述

外部表只能在Oracle 9i之后来使用。简单地说，外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，

我们可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。
外部表的特性
位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。
ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。
创建外部表的注意事项
1.需要先建立目录对象。

在建立对象的时候，需要小心，Oracle数据库系统不会去确认这个目录是否真的存在。如果在输入这个目录对象的时候，不小心把路径写错了，

那可能这个外 部表仍然可以正常建立，但是却无法查询到数据。由于建立目录对象时，缺乏这种自我检查的机制，为此在将路径赋予给这个目录对象时，需要特别的注意。

另外需 要注意的是路径的大小写。在Windows操作系统中，其路径是不区分大小写的。而在Linux操作系统，这个路径需要区分大小写。故在不同的操作系统 中，建立目录对象时需要注意这个大小写的差异。
2.对于操作系统文件的要求
建立外部表时，必须指定操作系统文件所使用的分隔符号。并且该分隔符有且只有一个。创建外部表时，不能含有标题列。

如果这个标题信息与外部表的字段类型不一致（如字段内容是number数据类型，而标题信息则是字符型数据，则在查询时就会出错）。

如果数据类型恰巧一致的话，这个标题信息Oracle数据库也会当作普通记录来对待。当Oracle数据库系统访问这个操作系统文件的时候，会在这个文件所在的目录自动创建一个日志文件。无论最后是否访问成功，这个日志文件都会如期建立。

查看这个日志文件，可以了解数据库访问外部表的频率、是否成功访问等等。默认情况下，该日志在与外部表的相同directory下产生。

3.在建立临时表时的相关限制

对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。
简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。而没有存储实际的数据。
由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。

4.删除外部表或者目录对象
一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象。
如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到"对象不存在"的错误信息。
查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。 如果只是在数据库层面上删除外部表，并不会自动删除操作系统上的外部表文件。

5.对于操作系统平台的限制
不同的操作系统对于外部表有不同的解释和显示方式
如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响

 

创建外部表：

1.创建目录对象：

select * from dba_directories;

drop directory DMP3;

create directory  DMP3 as '/tmp';

2.创建元数据：

在系统/tmp 下创建 test.txt 文件，存储数据

vi test.txt

3.创建外部表：

CREATE TABLE SMP.SN_TEMP
(
  SN  VARCHAR2(40 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DMP3
     ACCESS PARAMETERS
       ( records delimited by newline
   nobadfile
  nodiscardfile
  nologfile
    )
     LOCATION (DMP3:'test.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

 

5. 外部表使用：

之前外部表使用时无法和内部表做链接查询，发现外部表里字段竟包含了一个空格，所以使用时要截取字段，排除空格

select length(SN) from smp.SN_TEMP 

 

select substr("SN", 0,17) from smp.SN_TEMP

或者 select trim(sn) from smp.SN_TEMP