外部表的使用

   外部表允许数据库访问数据库之外的数据。外部表另关系模型超越了数据库的限制，如果java和xml是整合关系模式的最小方面，那么使用外部表就将机器带入了数据库，并永远改变了约定的规则

  1、首先我导出了部分数据，以供使用

180,Winston,Taylor,WTAYLOR
181,Jean,Fleaur,JFLEAUR
182,Martha,Sullivan,MSULLIVA
183,Girard,Geoni,GGEONI
184,Nandita,Sarchand,NSARCHAN
185,Alexis,Bull,ABULL
186,Julia,Dellinger,JDELLING
187,Anthony,Cabrio,ACABRIO
188,Kelly,Chung,KCHUNG
189,Jennifer,Dilly,JDILLY
190,Timothy,Gates,TGATES
...................

191,Randall,Perkins,RPERKINS
192,Sarah,Bell,SBELL
193,Britney,Everett,BEVERETT
194,Samuel,McCain,SMCCAIN
195,Vance,Jones,VJONES
196,Alana,Walsh,AWALSH
197,Kevin,Feeney,KFEENEY

2、创建一个directory以供oracle知道哪里能找到您的外部表

create directory t_employees as '/oracle/test';

3、创建外部表

  create table employees_t
  (employee_id number(9),
  first_name varchar2(32),
  last_name varchar2(32),
  email varchar2(96))
  organization external (
  type oracle_loader
  default directory t_employees
  access parameters(
  records delimited by newline
  fields terminated by ','
  (employee_id,first_name,last_name,email))
  location('exp.dat'))
 reject limit unlimited
 4、检测

SQL> desc employees_t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                                        NUMBER(9)
 FIRST_NAME                                         VARCHAR2(32)
 LAST_NAME                                          VARCHAR2(32)
 EMAIL                                              VARCHAR2(96)

SQL> select * from employees_t where rownum<10
 
EMPLOYEE_ID FIRST_NAME LAST_NAME                        EMAIL
----------- ---------- -------------------------------- ----------
        198 Donald     OConnell                         DOCONNEL
        199 Douglas    Grant                            DGRANT
        200 Jennifer   Whalen                           JWHALEN
        201 Michael    Hartstein                        MHARTSTE
        202 Pat        Fay                              PFAY
        203 Susan      Mavris                           SMAVRIS
        204 Hermann    Baer                             HBAER
        205 Shelley    Higgins                          SHIGGINS
        206 William    Gietz                            WGIETZ
 
9 rows selected.
ok，外部表建立成功！

但是外部表也存在一定的局限，不能修改表，执行dml操作， 不能建立索引等