Oracle Externale Tables

Creating External Tables You create external tables using the CREATE TABLE statement with an ORGANIZATION EXTERNAL clause.  This statement creates only metadata in the data dictionary.
實驗:建立外部表並載入資料；
EXAMPLE: Creating an External Table and Loading Data

In this example, the data for the external table resides in the two text files empxt1.dat and empxt2.dat.

The file empxt1.dat contains the following sample data:
資料一：
[oracle@chen test]$ cat empxt1.dat 
360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:
資料二：
[oracle@chen test]$ cat empxt2.dat 
401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

建立外部表語句如下：
[root@chen ~]# mkdir /flatfiles/{data,log,bad} -p
[root@chen ~]# chown oracle.oinstall /flatfiles/ -R

[oracle@chen test]$ cat create_external.sql
CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to hr 
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/flatfiles/data'; 
CREATE OR REPLACE DIRECTORY admin_log_dir 
    AS '/flatfiles/log'; 
CREATE OR REPLACE DIRECTORY admin_bad_dir 
    AS '/flatfiles/bad'; 
GRANT READ ON DIRECTORY admin_dat_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_log_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects. Provide the user password (hr) when prompted.
CONNECT hr
-- create the external table
CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4), 
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25), 
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25) 
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( employee_id, first_name, last_name, job_id, manager_id, 
           hire_date char date_format date mask "dd-mon-yyyy", 
           salary, commission_pct, department_id, email 
         ) 
       ) 
       LOCATION ('empxt1.dat', 'empxt2.dat') 
     ) 
     PARALLEL 
     REJECT LIMIT UNLIMITED; 

驗證資料
SQL> set linesize 1000
SQL> select * from  admin_ext_employees;
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID     MANAGER_ID HIRE_DATE     SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
----------- -------------------- ------------------------- ---------- ---------- --------- ---------- -------------- ------------- -------------------------
        401 Jesse                Cromwell                  HR_REP            203 17-MAY-01       7000              0            40 jcromwel
        402 Abby                 Applegate                 IT_PROG           103 17-MAY-01       9000             .2            60 aapplega
        403 Carol                Cousins                   AD_VP             100 17-MAY-01      27000             .3            90 ccousins
        404 John                 Richardson                AC_ACCOUNT        205 17-MAY-01       5000              0           110 jrichard
        360 Jane                 Janus                     ST_CLERK          121 17-MAY-01       3000              0            50 jjanus
        361 Mark                 Jasper                    SA_REP            145 17-MAY-01       8000             .1            80 mjasper
        362 Brenda               Starr                     AD_ASST           200 17-MAY-01       5500              0            10 bstarr
        363 Alex                 Alda                      AC_MGR            145 17-MAY-01       9000            .15            80 aalda

8 rows selected.

外部表不會在資料庫裡建立對應的表段；
SQL> select * from user_segments;
no rows selected

外部表只讀，不能進行DML操作
SQL> delete admin_ext_employees where employee_id=363;
delete admin_ext_employees where employee_id=363
       *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

無法透過analyze收集外部表的統計資訊(可以透過dbms_stats收集)
SQL> analyze table admin_ext_employees compute statistics;
analyze table admin_ext_employees compute statistics
              *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

SQL>  exec dbms_stats.gather_schema_stats('HR');
PL/SQL procedure successfully completed.

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！