【12c】Oracle 12c External Table 外部表

外部表在Oracle 9i中引入，外部表的特性是对现有SQL*Loader功能的补充，它能够使您访问外部数据源中的数据，就像访问数据库中的表一样。本篇就Oracle 12c中的外部表进行总结和演示，演示环境为Oracle Database 12c Enterprise Edition Release 12.1.0.2.0。

1 外部表介绍

外部表是通过create table ... organization external语句创建的，当创建外部表时，需指定如下属性：

• TYPE：指定外部表的类型，即访问驱动，访问驱动运行在服务器内部的，这点和SQL*Loader不同，有两种可用的类型，分别为：
  • ORACLE_LOADER：默认的访问驱动，它可以将数据从外部表加载到内部表，数据必须来自文本数据文件，但它不能将数据从内部表卸载到外部表；
  • ORACLE_DATAPUMP：该访问驱动可以执行数据的加载和卸载，数据必须来自二进制转储文件，从外部表加载到内部表是通过从二进制转储文件中提取来完成，而从内部表卸载到外部表是通过填充外部表的二进制转储文件来完成的。转储文件一旦创建，可以读取任意次数，但不能被修改，也就是说，不能执行任何DML语句；
• DEFAULT DIRECTORY：指定所有输入输出文件使用的默认目录，位置是一个目录对象，而不是一个目录路径，在创建外部表前必须先创建目录对象，否则，会出错；
• ACCESS PARAMETERS：描述外部数据源，并实现指定的外部表的类型，每一种类型的外部表都有自己的访问驱动程序，该驱动程序为该类型的外部表提供唯一的访问参数；
• LOCATION：指定外部表的数据文件，文件命名方式为directory:file，其中，目录是可选的，若不指定，默认为默认目录。

2 外部表创建

外部表的创建可以通过两种访问驱动实现，下面将分别介绍。

2.1 创建目录对象

SQL> ho mkdir /home/oracle/ext



SQL> create directory ext_dir as '/home/oracle/ext';



Directory created.

SQL> grant read,write on directory ext_dir to alen;



Grant succeeded.

2.2 ORACLE_LOAD访问驱动

2.2.1 手动创建外部表

创建外部表时如果数据文件不是文本文件，则需要使用preprocessor子句。

1）创建测试数据

[oracle@odd ext]$ cat emp.dat

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"

"7369","SMITH","CLERK","7902","1980/12/17","800.00","","20"

"7499","ALLEN","SALESMAN","7698","1981/2/20","1600.00","300.00","30"

"7521","WARD","SALESMAN","7698","1981/2/22","1250.00","500.00","30"

"7566","JONES","MANAGER","7839","1981/4/2","2975.00","","20"

"7654","MARTIN","SALESMAN","7698","1981/9/28","1250.00","1400.00","30"

"7698","BLAKE","MANAGER","7839","1981/5/1","2850.00","","30"

"7782","CLARK","MANAGER","7839","1981/6/9","2450.00","","10"

"7788","SCOTT","ANALYST","7566","1987/4/19","3000.00","","20