ORACLE外部表总结-阿里云开发者社区

开发者社区> 数据库> 正文

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.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;跳过一些限制。

clip_image001

SQL> alter table exter_test reject limit unlimited;
 
Table altered.
 
SQL> select * from exter_test;
 
        ID NAME         SEX             AGE      GRADE
---------- ------------ -------- ---------- ----------
     10001 kerry        male             28          1
     10002 jimmy        male             22          1
     10003 ken          male             21          1
     10004 merry        femal            20          1
 
SQL> 

clip_image002

此时去查看/oradata/exterltab,你会发现自动生成了EXTER_TEST_8907.bad 和 EXTER_TEST_8907.log两个文件,其中log记录访问外部表的记录信息,bad文件记录错误记录的信息。大体如下所示

[oracle@DB-Server exterltab]$ ls

EXTER_TEST_8907.bad EXTER_TEST_8907.log student.data

[oracle@DB-Server exterltab]$

clip_image003

 

3:查看外部表的目录

 

xxx_external_locations 可以知道当前所有的目录对象以及相关的外部表,还会查询出这些外部表所对应的操作系统文件的名字。

select * from all_external_locations;

select * from user_external_locations;

select * from dba_external_locations;

 

 
SQL> show user
USER is "SYS"
SQL> col owner for a20
SQL> col table_name for a30
SQL> col location for a30
SQL> 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       SYS DATA_FILE_DIR
ETL          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 table

eg: 在外部表上创建视图

SQL> create or replace view vv
  2  as 
  3  select * from etl.exter_test;
 
View created.
 
SQL> select * from vv;
 
        ID NAME         SEX             AGE      GRADE
---------- ------------ -------- ---------- ----------
     10001 kerry        male             28          1
     10002 jimmy        male             22          1
     10003 ken          male             21          1
     10004 merry        femal            20          1
 
SQL> 

 

外部表优势

如果要谈外部表的优势,一般会和SQLLDR来对比,外部表很多语法跟SQLLDR控制文件确实有很多类似的地方,下面谈谈自己的理解和"Oracel 9i&10g编程艺术"里面的一些对比

1. SQLLDR需要将数据装载入库后才能查询相关记录,如果只是为了查询一些记录,外部表确实比SQLLDR要有优势一些,很方便又不占用数据库存储空间。 尤其是很大的数据,以前做移动综合分析项目处理SGSN话单(几百G的数据,如果全部装载入库,非常浪费空间和时间)时就有这样的体会。外部表虚拟的导入 过程极快

2:当平面文件改变时,外部表内的数据会跟着改变。这样避免了插入、更新、删除等操作,对于超大记录的外部表相当有优势,我只需要使用Shell命令就能搞定数据库需要很高代价才能完成的事情。

3:外部表可以使用复杂的WHERE 条件有选择地加载数据。尽管SQLLDR 有一个WHEN 子句用来选择要加载的行,但是你只能使用AND 表达式和执行相等性比较的表达式,在WHEN 子句中不能使用区间(大于、小于),没有OR 表达式,也没有IS NULL 等。

4:能够合并(MERGE)数据。可以取一个填满数据的操作系统文件,并由它更新现有的数据库记录。

5:能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分。

6:使用INSERT 更容易地执行多表插入。从Oracle9i 开始,通过使用复杂的WHEN 条件,可以用一个INSERT 语句插入一个或多个表。尽管SQLLDR 也可以加载到多个表中,但是相应的语法相当复杂。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章