Oracle外部表格

標籤：oracle 外部表格

1.外部表格簡介

外部表格是Oracle9i之後來使用的。外部表格是一類表的定義存在於資料庫而資料不存在於資料庫的表。

在資料庫操作過程中可以對外部表格進行select,join,sort操作也可以對外部表格建立視圖和同義字。但是不能在外部表格上進行DML操作和建立索引。

外部表格提供兩種訪問驅動。一種ORACLE_LOADER另一種ORACLE_DATAPUMP。預設驅動是ORACLE_LOADER。

ORACLE_LOADER驅動從外部檔案中讀資料。ORACLE_LOADER建立外部表格的文法和SQL*Loader utility有異曲同工之妙。

ORACLE_DATAPUMP先通過外部表格unload出資料然後通過外部表格reload表。具體下面小節分析。

另外外部表格的統計資訊收集支援DBMS_STATS包但不支援ANALYZE。

而且外部表格不支援虛擬列。

2.建立外部表格

[[email protected] mydir]$ cat samp1.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

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

SQL> create or replace directory mydir as ‘/home/oracle/mydir‘;

Directory created.

SQL> grant read,write on directory mydir to scott;

Grant succeeded.

SQL> conn scott/tiger

Connected.

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 mydir 

       ACCESS PARAMETERS 

       ( 

         records delimited by newline 

         badfile mydir:‘badxt%a_%p.bad‘ 

         logfile mydir:‘logxt%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 (‘samp1.txt‘) 

     )

     PARALLEL REJECT LIMIT UNLIMITED;

     PARALLEL                 --指定查詢時的並行度

     REJECT LIMIT UNLIMITED  --指定外部表格查詢錯誤數量

執行成功看產生的日誌。

[[email protected] mydir]$ ls -al

total 20

drwxr-xr-x 2 oracle oinstall 4096 Nov 20 16:12 .

drwx------ 5 oracle oinstall 4096 Nov 20 15:53 ..

-rw-r--r-- 1 oracle oinstall 1413 Nov 20 16:12 logxt000_13688.log

-rw-r--r-- 1 oracle oinstall 1555 Nov 20 16:12 logxt000_16408.log

-rw-r--r-- 1 oracle oinstall  480 Nov 20 15:53 samp1.txt

[[email protected] mydir]$ cat logxt000_13688.log 

 LOG file opened at 11/20/15 16:12:03

Field Definitions for table ADMIN_EXT_EMPLOYEES

  Record format DELIMITED BY NEWLINE

  Data in file has same endianness as the platform

  Rows with all null fields are accepted

  Fields in Data Source: 

    EMPLOYEE_ID                     CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    FIRST_NAME                      CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    LAST_NAME                       CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    JOB_ID                          CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    MANAGER_ID                      CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    HIRE_DATE                       CHAR (80)

      Date datatype DATE, date mask dd-mon-yyyy

      Terminated by ","

      Trim whitespace same as SQL Loader

    SALARY                          CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    COMMISSION_PCT                  CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    DEPARTMENT_ID                   CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    EMAIL                           CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

[[email protected] mydir]$ cat logxt000_16408.log 

 LOG file opened at 11/20/15 16:12:03

Field Definitions for table ADMIN_EXT_EMPLOYEES

  Record format DELIMITED BY NEWLINE

  Data in file has same endianness as the platform

  Rows with all null fields are accepted

  Fields in Data Source: 

    EMPLOYEE_ID                     CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    FIRST_NAME                      CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    LAST_NAME                       CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    JOB_ID                          CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    MANAGER_ID                      CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    HIRE_DATE                       CHAR (80)

      Date datatype DATE, date mask dd-mon-yyyy

      Terminated by ","

      Trim whitespace same as SQL Loader

    SALARY                          CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    COMMISSION_PCT                  CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    DEPARTMENT_ID                   CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

    EMAIL                           CHAR (255)

      Terminated by ","

      Trim whitespace same as SQL Loader

Date Cache Statistics for table ADMIN_EXT_EMPLOYEES

  Max Size:      1000

  Entries :         1

  Hits    :         7

  Misses  :         0

logxt000_16408.log日誌比logxt000_13688.log尾部多了一些統計資訊。

查看外部表格

SQL>  select EMPLOYEE_ID,FIRST_NAME from ADMIN_EXT_EMPLOYEES;

EMPLOYEE_ID FIRST_NAME

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

360 Jane

361 Mark

362 Brenda

363 Alex

401 Jesse

402 Abby

403 Carol

404 John

3.外部表格修改

能夠使用於alter table下面子句來修改外部表格訪問參數

reject limit:ALTER TABLE admin_ext_employees REJECT LIMIT n;

project column: ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED;

ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

default dectory:ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

查看修改的屬性

SQL> select OWNER,TABLE_NAME,REJECT_LIMIT,DEFAULT_DIRECTORY_NAME,PROPERTY from dba_external_tables;

OWNER   TABLE_NAME  REJECT_LIM DEFAULT_DIRECTORY_NAME    PROPERTY

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

SCOTT   ADMIN_EXT_EMPLOYEES  UNLIMITED  MYDIR    ALL

4.預先處理外部表格

SQL> create or replace directory mydir as ‘/home/oracle/mydir‘;

Directory created.

SQL> grant read,write,execute on directory mydir to scott;

Grant succeeded.

在/home/oracle/mydir下面建立一個檔案

[[email protected] mydir]$ cat uncompress 

/bin/gzip -cd $1

[[email protected] mydir]$ chmod +x uncompress

SQL> conn scott/tiger   

Connected.

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 mydir 

       ACCESS PARAMETERS 

       ( 

         records delimited by newline 

PREPROCESSOR exec_file_dir:‘uncompress‘

  badfile mydir:‘badxt%a_%p.bad‘ 

         logfile mydir:‘logxt%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 (‘samp1.txt.gz‘) 

     )

     PARALLEL                 

     REJECT LIMIT UNLIMITED;

5.外部表格的刪除使用DROP TABLE語句這條語句僅僅刪除資料庫中的介質,對實際資料沒有影響。

SQL> drop table admin_ext_employees;

Table dropped.

6.有關外部表格的系統許可權和對象。

外部表格的系統許可權

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

SELECT ANY TABLE

外部表格的對象許可權

ALTER

SELECT

本文出自 “知行合一” 部落格，請務必保留此出處http://dovelauren.blog.51cto.com/9876026/1715857

Oracle外部表格