external table

oracle10g R2 administrator guide对external table的定义

External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided.

可以对外部表进行查询,但不能进行DML(insert,update,delete)操作

10g提供了两种access dirver

     oracle_loader:只能进行load

     oracle_data:可以进行load/unload

external table创建语法:create table … organization external…

下面分别对这两access driver举例

创建directory

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

Directory created.

授权给oak

SQL> grant read,write on directory ext to oak;

Grant succeeded.

例子一:

info.dat文件内容如下:

zheng,jabba@163.com,15976013099

zhang,homer@163.com,13825683251

li,superman@163.com,13013718061

zhao,billythkid@163.com,15013684477

sun,nobody@163.com,18836015678

zhou,crustacean@163.com,13666778245

创建external table

SQL> create table ext_info

  2  (

  3  name varchar2(10),

  4  email varchar2(50),

  5  phone varchar2(11)

  6  )

  7  organization external

  8  (

  9  type oracle_loader

 10  default directory ext

 11  access parameters

 12  (

 13  records delimited by newline

 14  fields terminated by ','

 15  )

 16  location ('info.dat')

 17  )

 18  reject limit unlimited;

Table created.

接下来你可以对它进行查询了.

例子二:

创建用于产生external table data的表

SQL> create table oak.object as select * from dba_objects;

Table created.

创建external table

SQL> conn oak

Enter password:

Connected.

SQL> create table ext_object

  2  organization external

  3  (

  4  type oracle_datapump

  5  default directory ext

  6  location ('ext_object.dump')

  7  )

  8  reject limit unlimited

  9  as select * from object;

Table created.

注: The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.