oracle外部表

外部表介绍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数据库若需要存取文件系统，就必须使用目录对象，以相对路径方式存取文件，强化数据库的安全性。

建立目录对象、授予权限。

SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab';给用户授予指定目录的操作权限SQL>GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;2：创建外部表我创建了一个平面文件作为测试用例，如下所示，总共5条记录，其中一条错误记录[oracle@DB-Server exterltab]$ more student.data10001@#$kerry@#$male@#$28@#$110002@#$jimmy@#$male@#$22@#$110003@#$ken@#$male@#$21@#$110004@#$merry@#$femal@#$20@#$1this is a bad fileCREATE TABLE EXTER_TEST(ID NUMBER(5) ,NAME VARCHAR(12) ,SEX VARCHAR(8) ,AGE NUMBER(3) ,GRADE NUMBER(1)) ORGANIZATION EXTERNAL(type oracle_loaderdefault directory dump_diraccess parameters(records delimited by newlinefields terminated by'@#$')location ('student.data'));外部表语法也是蛮复杂的，参数选项非常多，这里不做过多解释。

有兴趣自然可以翻阅官方文档。

SQL> select * from exter_test;select * from exter_test*ERROR at line 1:ORA-29913: error in executing ODCIEXTTABLEFETCH calloutORA-30653: reject limit reachedORA-06512: at "SYS.ORACLE_LOADER", line 52出现下面错误，是因为student.data文件中有不符合规范的记录，可以删除“this is a bad file”这一条记录，但是这是为了测试下面情况，所以可以通过执行 alter table exter_test reject limit unlimited;跳过一些限制。

SQL> alter table exter_test reject limit unlimited;Table altered.SQL> select * from exter_test;ID NAME SEX AGE GRADE---------- ------------ -------- ---------- ----------10001 kerry male 28 110002 jimmy male 22 110003 ken male 21 110004 merry femal 20 1SQL>此时去查看/oradata/exterltab,你会发现自动生成了EXTER_TEST_8907.bad 和EXTER_TEST_8907.log两个文件,其中log记录访问外部表的记录信息，bad文件记录错误记录的信息。

大体如下所示[oracle@DB-Server exterltab]$ lsEXTER_TEST_8907.bad EXTER_TEST_8907.log student.data[oracle@DB-Server exterltab]$3:查看外部表的目录xxx_external_locations 可以知道当前所有的目录对象以及相关的外部表，还会查询出这些外部表所对应的操作系统文件的名字。

select * from all_external_locations;select * from user_external_locations;select * from dba_external_locations;SQL> show userUSER is "SYS"SQL> col owner for a20SQL> col table_name for a30SQL> col location for a30SQL> col directory_owner for a3;SQL> col directory_name for a30;SQL> select * from dba_external_locations;OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME--------- -------------------------- ------------------------------------------SH SALES_TRANSACTIONS_EXT sale1v3.dat SYSDATA_FILE_DIRETL EXTER_TEST student.data SYS DUMP_DIR 4:查看外部表的详细信息select * from user_external_tables;select * from all_external_tables;select * from dba_external_tables;SQL> desc dba_external_tables;Name Null? Type----------------------------------------- ------------------------------------OWNER NOT NULL VARCHAR2(30)TABLE_NAME NOT NULL VARCHAR2(30)TYPE_OWNER CHAR(3)TYPE_NAME NOT NULL VARCHAR2(30)DEFAULT_DIRECTORY_OWNER CHAR(3)DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)REJECT_LIMIT VARCHAR2(40)ACCESS_TYPE VARCHAR2(7)ACCESS_PARAMETERS VARCHAR2(4000)PROPERTY VARCHAR2(10) 5：删除外部表删除外部表SQL语法跟普通表一样，但是不同之处在于有可能还要删除与之对应的目录对象。

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

不过在删除这些内容时会有一些限制。

这些限制主要是管理上的限制，而不是技术上的限制。

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

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

如要先删除外部表，然后再删除目录对象。

有时候一个目录对象中可能会包含多个外部表。

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

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

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

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

此时查询这个外部表的时候，系统就会提示“对象不存在”的错误信息。

所以这个删除目录对象时，数据库系统缺乏一种检查，此时只有数据库管理员在删除目录对象时，先手工确认一下这个目录对象是否存在其他的外部表。

外部表限制：1. 只能对表进行SELECT，不能进行DELETE、UPDATE、INSERT这些DML操作。

2. 因为外部表需要在ORACLE数据库“服务端”创建目录，OS文件必须放在这些目录中。

即这些文件只能放在数据库服务端。

如果数据文件不位于服务器，则无法使用外部表3. 外部表上不能创建索引。

但可以建立视图4. 外部表不支持LOB对象。

如果要使用LOB类型，则不能使用外部表。

eg：删除外部表的记录SQL> delete from exter_test where id=10001;delete from exter_test where id=10001*ERROR at line 1:ORA-30657: operation not supported on external organized tableeg: 在外部表上创建视图SQL> create or replace view vv2 as3 select * from etl.exter_test;View created.SQL> select * from vv;ID NAME SEX AGE GRADE---------- ------------ -------- ---------- ----------10001 kerry male 28 110002 jimmy male 22 110003 ken male 21 110004 merry femal 20 1SQL>外部表优势：如果要谈外部表的优势，一般会和SQLLDR来对比，外部表很多语法跟SQLLDR控制文件确实有很多类似的地方，下面谈谈自己的理解和"Oracel 9i&10g编程艺术"里面的一些对比1. SQLLDR需要将数据装载入库后才能查询相关记录，如果只是为了查询一些记录，外部表确实比SQLLDR要有优势一些，很方便又不占用数据库存储空间。