外部表（EXTERNAL TABLE）

来自：http://blog.itpub.net/post/12524/461332

external table可以实现将flat file的内容不用真正装载到数据库，就跟使用数据库内部的表一样来使用。只是不能对这种类型的表进行DML和INDEX操作。这样方便我们在flat file和数据库表之间load和unload数据。

这个功能在oracle 9i上就实现了，当时仅仅使用oracle_loader引擎来从外部读取数据，到了10G增加了另一种方式ORACLE_DATAPUMP，既可以读这种格式的外部文件，也可以将内部表的数据保存为外部文件。

创建external table:

假设我们有下面内容的flat file:

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

建立目录并且赋予test对这个目录的读写权限。

SQL> conn /as sysdba
Connected

SQL> create or replace directory test_dir as '/export/home/oracle';

Directory created.

SQL> grant read,write on directory test_dir to test;

Grant succeeded.

创建exteral table:

SQL> conn test/xxxx
Connected.
SQL> create table test_ext
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 (TYPE ORACLE_LOADER
15 DEFAULT DIRECTORY test_dir
16 ACCESS PARAMETERS
17 (
18 records delimited by newline
19 fields terminated by ','
20 )
21 LOCATION ('test.dat'))
22 REJECT LIMIT UNLIMITED;

Table created.

SQL> select * from test_ext;

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

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID
----------- -------------------- ------------------------- ---------- ----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
------------ ---------- -------------- ------------- -------------------------
363 Alex Alda AC_MGR 145
17-MAY-01 9000 .15 80 aalda

在创建external表时不做检查的，如果有问题，在查询时报错，同时查日志确认，默认会在test_dir放置bad,log等文件,通过这些文件能够知道如何工作及其有什么问题。

到10G我们可以将表的数据unload成oracle_datadump格式的文件。方便我们数据传送。

将内部的表保存到外部文件tet_dir.dmp,格式是oracle_datapump.

1 create table test_ext1
2 organization external
3 (type oracle_datapump
4 default directory test_dir
5 location('test_dir.dmp')
6* ) as select * from test_ext
SQL> /

Table created.

这样我们就生成一个oracle_datapump格式的文件，然后将这个文件传送到本地或其他数据库，用下面格式导入即可：

1 create table imp_test_ext1
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 (TYPE ORACLE_DATAPUMP
15 DEFAULT DIRECTORY test_dir
16* LOCATION ('test_dir.dmp'))
SQL> /

Table created.

SQL> select * from imp_test_ext1;

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

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID
----------- -------------------- ------------------------- ---------- ----------
HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
------------ ---------- -------------- ------------- -------------------------
363 Alex Alda AC_MGR 145
17-MAY-01 9000 .15 80 aalda

这里我们发现使用oracle_datapump比oracle_loader语法要简单很多，格式说明是内包含的。

转摘一篇好文章：http://blog.chinaunix.net/uid-10697776-id-2935685.html

分类：

定义

Externaltablesaccessdatainexternalsourcesasifitwereinatableinthedatabase.

YoucanconnecttothedatabaseandcreatemetadatafortheexternaltableusingDDL.

TheDDLforanexternaltableconsistsoftwoparts:onepartthatdescribestheOracle

columntypes,andanotherpart(theaccessparameters)thatdescribesthemappingof

theexternaldatatotheOracledatacolumns.

u创建的语法类似于："CREATETABLE...ORGANIZATIONEXTERNAL"

u数据在数据库的外部组织，是操作系统文件。

u操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

u数据是只读的。（外部表相当于一个只读的虚表）

u不可以在上面运行任何DML操作，不可以创建索引。

u可以查询操作和连接。可以并行操作。

建立外部表的步骤：

1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test”

2、创建一个Directory：

createdirectoryTestTable_diras'D:\Test';

3、创建一个外部表：

createtableTestTable(

IDvarchar2(10),

NAMEvarchar2(20),

TYPEvarchar2(20),

AGEvarchar2(20))

organizationexternal(

typeoracle_loader

defaultdirectoryTestTable_dir

accessparameters(fieldsterminatedby',')

location('TestTable.csv')

);

各类参数说明

1、typeoracle_loader

数据转换驱动器，oracle_loader为默认，也可以改换其他

2、defaultdirectoryTestTable_dir

location('TestTable.csv')

指定外部表所在文件夹以及指定文件

3、accessparameters

设置转换参数，例如(fieldsterminatedby',')表示以','为字段间的分隔符

●参数由访问驱动程序定义

外部表的错误处理

1、REJECTLIMIT子句

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。

*默认的REJECTLIMIT值为0

*REJECTLIMITUNLIMITED则不会报错

2、BADFILE和NOBADFILE子句

在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中

使用NOBADFILE子句则表示忽略转换错误的数据

●如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件

●BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。

3、LOGFILE和NOLOGFILE子句

在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

使用NOLOGFILE子句则表示不记录错误信息到log中

●如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件

修改外部表语句

外部表与堆表一样可以之用ALTERTABLE命令修改表属性

*REJECTLIMIT--错误数

*DEFAULTDIRECTORY--默认目录

*ACCESSPARAMETERS--参数

*LOCATION--数据文件

*ADDCOLUMN--增加列

*MODIFYCOLUMN--列定义

*DROPCOLUMN--删除列

*RENAMETO--外部表更名

其他约束

●外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。

●外部表不能建立索引，如要建立，则需要先createtableXXasselect*fromTestTable

PS:

1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。

create table all_objects_unload
organization external
(
type oracle_datapump
default directory testdir
location('allobjects.dat')
)
as
select * from all_objects