MyException - 我的异常网
当前位置:我的异常网» 数据库 » oracle外部表的应用

oracle外部表的应用

www.MyException.Cn  网友分享于:2013-02-21  浏览:8次
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 平面文件数据的描述
  
  假设如下的两个平面文件
  
  1)dat:
  
  7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
  7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
  7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
  7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
  
  2)dat:
  
  7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
  7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
  7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
  
  (要有对操作系统中该目录的读写权限。)
  
  2 创建一个逻辑目录并进行适当授权:
  
  SQL> CREATE DIRECTORY TESTDIR AS 'D:\TEMP\';
  目录已创建。
  
  SQL> GRANT READ ON DIRECTORY TESTDIR TO DEMO;
  授权成功。
  
  SQL> GRANT WRITE ON DIRECTORY TESTDIR TO DEMO;
  授权成功。
  
  注意:创建完毕逻辑目录之后要把平面文件拷贝到该目录下,另外还要注意文件名字不要写错。

3 创建外部表
  
  SQL> ED
  已写入文件 afiedt.buf
  
   CREATE TABLE DEMO.EXT
   (emp_id number(4),
   ename varchar2(12),
   job varchar2(12) ,
   mgr_id number(4) ,
   hiredate date,
   salary number(8),
   comm number(8),
   dept_id number(2))
   ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
   DEFAULT DIRECTORY TESTDIR
   ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE
   FIELDS TERMINATED BY ',')
   LOCATION('1.DAT','2.DAT'))
 
  
  表已创建。
  
  4 进行Select选择操作看看是否正确:
  
  SQL> select * from DEMO.EXT;
  
  EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
  ------ ------- ---------- ------- --------- -------- ---------- -------
  7369 SMITH CLERK 7902 17-DEC-80 100 0 20
  7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
  7521 WARD SALESMAN 7698 22-FEB-81 450 0 30
  7566 JONES MANAGER 7839 02-APR-81 1150 0 20
  7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
  7698 BLAKE MANAGER 78
39 01-MAY-81 1550 0 30

要得到外部表的有关信息:
    
  SQL> SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERS
  2 FROM
  3 DBA_EXTERNAL_TABLES;
  
  OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_PARAMETERS
  ------------------ ------------------------------ ------------------------------
  DEMO EXT TESTDIR RECORDS DELIMITED BY NEWLINE
  FIELDS TERMINATED BY ','
  
  如果DBA想要知道平面文件的位置,使用如下的查询:
  
  SQL> select * from DBA_EXTERNAL_LOCATIONS;
  
  OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
  ------------------------------------
  DEMO EXT 1.DAT SYS TESTDIR
  DEMO EXT 2.DAT SYS TESTDIR

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有