Oracle 外部表

最新推荐文章于 2024-08-02 15:49:51 发布

coco3600

最新推荐文章于 2024-08-02 15:49:51 发布

阅读量150

点赞数

文章标签：数据库面试 shell

Oracle 外部表

--=================

-- Oracle 外部表

--=================

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

可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。

一、外部表的特性

位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。

对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。

外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。

ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。

二、创建外部表的注意事项

1.需要先建立目录对象

2.对于操作系统文件的要求

文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件

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

对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。

对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。

建议不用使用特殊的列标题字符

在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。

创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。

简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。而没有存储实际的数据。

由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。

4.删除外部表或者目录对象

一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象。

如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到"对象不存在"的错误信息。

查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。

5.对于操作系统平台的限制

不同的操作系统对于外部表有不同的解释和显示方式

如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。

建议避免不同操作系统以及不同字符集所带来的影响

三、创建外部表

使用CREATE TABLE语句的ORGANIZATION EXTENERAL子句来创建外部表。外部表不分配任何盘区，因为仅仅是在数据字典中创建元数据。

1.外部表的创建语法

create table table_name

(col1 datatype1,col2 datatype2 ,col3 datatype3)

organization exteneral

(.....)

2.由查询结果集，使用Oracle_datapump来填充数据来生成外部表

a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限

[oracle@oradb ~]$ mkdir -p /home/oracle/external_tb/data

sys@ORCL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';

sys@ORCL> grant read,write on directory dat_dir to scott;

b.创建外部表

scott@ORCL> create table ex_tb1 --创建外部表

2 (ename,job,sal,dname) --表列描述，注意未指定数据类型

3 organization external

4 (

5 type oracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select生成，故不支持oracle_loader

6 default directory dat_dir --指定外部表的存放目录

7 location('tb1.exp','tb2.exp') --产生外部表的内容将填充到这些文件中

8 )

9 parallel --按并行方式来填充

10 as

11 select ename,job,sal,dname -填充使用的原始数据

12 from emp join dept

13 on emp.deptno=dept.deptno;

c.--验证外部表

scott@ORCL> select * from ex_tb1;

ENAME JOB SAL DNAME

---------- --------- ---------- --------------

SMITH CLERK 800 RESEARCH

ALLEN SALESMAN 1600 SALES

WARD SALESMAN 1250 SALES

JONES MANAGER 2975 RESEARCH

..........

对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。

3.使用SQLLDR提供外部表的定义并创建外部表

关于SQL*Loader的使用请参照：SQL*Loader使用方法

我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义

[oracle@oradb ~]$ cat demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT_NEW

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only

EXTERNAL_TABLE 参数有以下三个值：

NOT_USED：默认值。

EXECUTE：这个值说明SQLLDR不会生成并执行一个SQL INSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。

GENERATE_ONLY：使SQLLDR 并不具体加载任何数据，而只是会生成所执行的SQL DDL 和DML 语句，并放到它创建的日志文件中。

注：DIRECT=TRUE 覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。

[oracle@oradb ~]$ cat demo1.log --查看sqlldr产生的日志文件

Table DEPT_NEW, loaded from every logical record.

Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

DEPTNO FIRST * , CHARACTER

DNAME NEXT * , CHARACTER

LOC NEXT * , CHARACTER

CREATE DIRECTORY statements needed for files --创建一个目录

------------------------------------------------------------------------

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/'

CREATE TABLE statement for external table: --生成创建外部表的命令

------------------------------------------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"

(

"DEPTNO" NUMBER(2),

"DNAME" VARCHAR2(20),

"LOC" VARCHAR2(20)

)

ORGANIZATION external --该子句表明是一个外部表 heap 对应普通表，index 对应iot，external 对应外部表

(

TYPE oracle_loader --说明外部文件访问方式：oracle_loader或oracle_datapump(9i不支持)

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目录

ACCESS PARAMETERS --这个访问参数有些类似于sqlldr中控制文件中的描述信息

( --系统根据这些描述信息来生成外部表的格式

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --记录默认以换行符结束

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放处理失败的记录文件描述

LOGFILE 'demo1.log_xt' --日志文件

READSIZE 1048576 --Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式则从SGA分配

SKIP 6 --跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过

FIELDS TERMINATED BY "," LDRTRIM --描述字段的终止符

REJECT ROWS WITH ALL NULL FIELDS --所有为空值的行被跳过并且记录到bad file.

( --下面是描述外部文件各个列的定义

"DEPTNO" CHAR(255)

TERMINATED BY ",",

"DNAME" CHAR(255)

TERMINATED BY ",",

"LOC" CHAR(255)

TERMINATED BY ","

)

location

(

'demo1.ctl' --描述外部文件的文件名

)

)REJECT LIMIT UNLIMITED --描述允许的错误数，此处为无限制

INSERT statements used to load internal tables: --用于将数据填充到表，使用append方式

------------------------------------------------------------------------

INSERT /*+ append */ INTO DEPT_NEW

(

DEPTNO,

DNAME,

LOC

)

SELECT

"DEPTNO",

"DNAME",

"LOC"

FROM "SYS_SQLLDR_X_EXT_DEPT"

statements to cleanup objects created by previous statements: --用于删除目录和外部表的定义信息

------------------------------------------------------------------------

DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

-----------------------------------------------------------------------------------------------------------------------

sys@ORCL> grant create any directory to scott;

sys@ORCL> grant drop any directory to scott;

scott@ORCL> create table dept_new

2 (deptno number,dname varchar2(20),loc varchar2(25));

scott@ORCL> select * from dept_new;

no rows selected

[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute

scott@ORCL> select * from dept_new;

DEPTNO DNAME LOC

---------- -------------------- -------------------------

10 Sales Virginia

20 Accounting Virginia

30 Consulting Virginia

40 Finance Virginia

4.使用平面文件定义并生成外部表

a.平面文件数据

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

b.继续使用前面创建的目录/home/oracle/external_tb/data 来存放数据文件：

sys@ORCL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

--------------- --------------- ---------------------------------------------

SYS DATA_PUMP_DIR /u01/oracle/10g/rdbms/log/

SYS DAT_DIR /home/oracle/external_tb/data/

scott@ORCL> ho ls /home/oracle/external_tb/data/

1.dat 2.dat tb1.exp tb2.exp

c.创建外部表

scott@ORCL> get /u01/bk/scripts/tb.emp_new

1 create table emp_new

2 (

3 emp_id number(4),

4 ename varchar2(15),

5 job varchar2(12) ,

6 mgr_id number(4) ,

7 hiredate date,

8 salary number(8),

9 comm number(8),

10 dept_id number(2)

11 )

12 organization external

13 (

14 type oracle_loader

15 default directory dat_dir

16 access parameters

17 (

18 records delimited by newline

19 fields terminated by ','

20 )

21 location

22 ('1.dat','2.dat')

23* );

scott@ORCL> start /u01/bk/scripts/tb.emp_new

d.验证外部表　　

scott@ORCL> select * from emp_new;

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

............................

scott@ORCL> delete from emp_new where ename='SMITH'; --外部表不能执行DML

delete from emp_new where ename='SMITH'

ERROR at line 1:

ORA-30657: operation not supported on external organized table

scott@ORCL> insert into emp_new(emp_id,ename) select 8888,'Robinson' from dual;

insert into emp_new(emp_id,ename) select 8888,'Robinson' from dual

ERROR at line 1:

ORA-30657: operation not supported on external organized table

　　 e.获得外部表的有关信息：

scott@ORCL> col access_parameters format a35

scott@ORCL> select owner,table_name,type_name,default_directory_name,access_parameters

2 from dba_external_tables;

OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS

---------- --------------- ------------------------------ --------------- -----------------------------------

SCOTT EX_TB1 ORACLE_DATAPUMP DAT_DIR

SCOTT EMP_NEW ORACLE_LOADER DAT_DIR records delimited by newline

fields terminated by ','

SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimited by newline

fields terminated by ','

　　 f.获得平面文件的位置，使用如下的查询：

scott@ORCL> select * from dba_external_locations order by table_name;

OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME

---------- --------------- --------------- --- ------------------------------

SCOTT EMP_NEW 1.dat SYS DAT_DIR

SCOTT EMP_NEW 2.dat SYS DAT_DIR

SCOTT EMP_PUMP 1.dat SYS DAT_DIR

SCOTT EMP_PUMP 2.dat SYS DAT_DIR

SCOTT EX_TB1 tb2.exp SYS DAT_DIR

SCOTT EX_TB1 tb1.exp SYS DAT_DIR

5.外部表定义的进一步分析

CREATE TABLE external_table

(

COL01 VARCHAR2(100),

COL02 NUMBER,

......

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY "XXX"

ACCESS PARAMETERS

(

RECORDS DELIMITED BY 0X'0A'

SKIP 1

BADFILE 'bad.txt'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LRTRIM MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

)

LOCATION

("CJ_DIR":'data.txt')

)REJECT LIMIT UNLIMITED;

外部表定义的几个重点

a.ORGANIZATION EXTERNAL 关键字，必须要有。以表明定义的表为外部表。

b.重要参数外部表的类型

ORACLE_LOADER ：定义外部表的缺省方式，只能只读方式实现文本数据的装载。

ORACLE_DATAPUMP ：支持对数据的装载与卸载，数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表，也

可以从内部表卸载数据作为二进制文件填充到外部表。

c.DEFAULT DIRECTORY ：缺省的目录指明了外部文件所在的路径

d.LOCATION ：定义了外部表的位置

f.ACCESS PARAMETERS ：描述如何对外部表进行访问

RECORDS关键字后定义如何识别数据行

DELIMITED BY 'XXX' ——换行符，常用newline定义换行，并指明字符集。对于特殊的字符则需要单独定义，

如特殊符号，可以使用OX'十六位值'，例如tab(/t)的十六位是9，则DELIMITED BY 0X'09'；

cr(/r)的十六位是d，那么就是DELIMITED BY 0X'0D'。

SKIP X ——跳过X行数据，有些文件中第一行是列名，需要跳过第一行，则使用SKIP 1。

FIELDS关键字后定义如何识别字段，常用的如下：

FIELDS：TERMINATED BY 'x' ——字段分割符。

ENCLOSED BY 'x' ——字段引用符，包含在此符号内的数据都当成一个字段。

例如一行数据格式如："abc","a""b,""c,"。使用参数TERMINATED BY ',' ENCLOSED BY '"'后，系统会读到两个字段，

第一个字段的值是abc，第二个字段值是a"b,"c,。

LRTRIM ——删除首尾空白字符。

MISSING FIELD VALUES ARE NULL ——某些字段空缺值都设为NULL。

对于字段长度和分割符不确定且准备用作外部表文件，可以使用UltraEdit、Editplus等来进行分析测试，如果文件较

大，则需要考虑将文件分割成小文件并从中提取数据进行测试。

外部表对错误的处理

REJECT LIMIT UNLIMITED

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制

BADFILE 和NOBADFILE 子句

用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误

如果未指定该参数，则系统自动在源目录下生成与外部表同名的.BAD文件

BADFILE记录本次操作的结果，下次将会被覆盖

LOGFILE 和NOLOGFILE 子句

同样在accessparameters中加入LOGFILE 'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

而NOLOGFILE子句则表示不记录错误信息到log中，如忽略该子句，系统自动在源目录下生成与外部表同名的.LOG文件

注意以下几个常见的问题

1.外部表经常遇到BUFFER不足的情况，因此尽可能的增大READ SIZE

2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样，碰到错误日志提示如是换行符问题，可以使用

UltraEdit打开，直接看十六进制

3.特定行报错时，查看带有"BAD"的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突

外部表介绍

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

 
      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>  
     

此时去查看/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]$

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 也可以加载到多个表中，但是相应的语法相当复杂。

参考资料：

http://blog.itpub.net/22578826/viewspace-703470

http://www.cnblogs.com/lanzi/archive/2010/12/28/1918755.html

http://blog.csdn.net/leshami/article/details/6078481

About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。