oracle中导外表,学习掌握oracle外表(external table)

定义

External tables access data inexternal sourcesas if it were in a table in the database.

You can connect to the database and create metadata for the external tableusing DDL.

The DDL for an external table consists of two parts:one part that describes the Oracle

column types, andanother part (the access parameters) that describes the mapping of

the external data to the Oracle data columns.

u创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

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(fields terminatedby',')

location('TestTable.csv')

);

各类参数说明

1、typeoracle_loader

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

2、defaultdirectoryTestTable_dir

location('TestTable.csv')

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

3、accessparameters

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

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

外部表的错误处理

1、REJECT LIMIT子句

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

* 默认的REJECT LIMIT值为0

* REJECT LIMIT UNLIMITED则不会报错

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文件

修改外部表语句

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

* REJECT LIMIT        --错误数

* DEFAULT DIRECTORY   --默认目录

* ACCESS PARAMETERS   --参数

* LOCATION            --数据文件

* ADD COLUMN          --增加列

* MODIFY COLUMN       --列定义

* DROP COLUMN         --删除列

* RENAME TO           --外部表更名

其他约束

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

● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable

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