oracle外部表.docVIP

外部表介绍 ? 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数据库若需要存取文件系统，就必须使用目录对象，以相对路径方式存取文件，强化数据库的安全性。建立目录对象、授予权限。 SQLCREATE OR REPLACE DIRECTORY DUMP_DIR AS /oradata/exterltab; 给用户授予指定目录的操作权限 SQLGRANT 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;跳过一些限制。 S