Oracle外部表

season22

　　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.创建外部表
　　[oracle@localhost 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  --指定外部表查询错误数量
　　执行成功看产生的日志。
　　[oracle@localhost 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
　　[oracle@localhost 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
　　[oracle@localhost 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>　　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 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下面创建一个文件
　　[oracle@localhost mydir]$ cat uncompress
　　/bin/gzip -cd $1
　　[oracle@localhost 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