Oracle建立外部表格

Oracle建立外部表格

Oracle db允許以唯讀形式查詢外部表格。外部表格可以儲存在任何oracle db可以讀取的存放裝置中，其內容不在db中儲存，db只儲存external table的metadata，db可以查詢（join、sort）external table，可以建立view、synonym，但不可以執行DML語句。

建立外部表格文法create table ......orginzition external，可以把外部表格假想為一個view，可以正常的做select。

analyze 和 虛擬列 表分析不使用與外部表格

建立外部表格文法

外部表格檔案：

example1.txt

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
example2.txt
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

建立外部表格oracle可識別路徑：

SQL> conn / as sysdba
SQL> create directory external_dir as '/u01/app/oracle/oradata/external';
 
Directory created.
 
SQL> grant read，write on directory external_dir to bkjia;
 
Grant succeeded.

外部表格的使用使用者bkjia，建立外部表格

  1      SQL> CREATE TABLE  ex_employees
  2                    (employee_id      NUMBER(4),
  3                      first_name        VARCHAR2(20),
  4                      last_name        VARCHAR2(25),
  5                      job_id            VARCHAR2(10),
  6                      manager_id        NUMBER(4),
  7                      hire_date        DATE,
  8                      salary            NUMBER(8,2),
  9                      commission_pct    NUMBER(2,2),
10                      department_id    NUMBER(4),
11                      email            VARCHAR2(25)
12                    )
13      ORGANIZATION EXTERNAL
14      (
15        TYPE ORACLE_LOADER
16        DEFAULT DIRECTORY external_dir
17        ACCESS PARAMETERS
18        (
19          records delimited by newline
20          badfile external_dir:'empxt%a_%p.bad'
21          logfile  external_dir:'empxt%a_%p.log'
22          fields terminated by ','
23          missing field values are null
24          ( employee_id, first_name, last_name, job_id, manager_id,
25            hire_date char date_format date mask "dd-mon-yyyy",
26            salary, commission_pct, department_id, email
27          )
28        )
29        LOCATION ('example1.txt', 'example2.txt')
30      )
31      PARALLEL
32      REJECT LIMIT UNLIMITED;
 
Table created.

建立完成後可以可以通過cats把資料匯入至資料庫的表中，如果資料比較多，可以開啟session level parallel匯入

alter session enable parallel；
create table employee as select * from ex_employee;
 
EMPLOYEE_ID FIRST_NAME          LAST_NAME                JOB_ID    MANAGER_ID HIRE_DATE    SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
----------- -------------------- ------------------------- ---------- ---------- --------- ---------- -------------- ------------- -------------------------
        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
 
        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
 
 
10 rows selected.

建立完成，外部表格可以正常使用