External Tables

官方文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2201774

external_table_clause::=

Description of the illustrationexternal_table_clause.gif

 

(external_data_properties::=)

external_data_properties::=

Description of the illustrationexternal_data_properties.gif

---由上边的语法可以看出：ACCESS PARAMETERS可以省略。然而DEFAULTDIRECTORY指定文件所在的目录必须要有，还有LOCATION也一样。

 

 

 

external_table_clause--下边是各个参数的说明

 

Use the external_table_clause to createan external table, which is a read-only table whose metadata isstored in the database but whose data in stored outside thedatabase. Among other capabilities, external tables let you querydata without first loading it into the database.

See Also:

OracleData Warehousing Guide, OracleDatabase Administrator's Guide, and Oracle Database Utilities for information on the usesfor external tables

Because external tables have no data in the database, you definethem with a small subset of the clauses normally available whencreating tables.

• Within the relational_properties clause,you can specify only column anddatatype.

• Within the physical_properties_clause, youcan specify only the organization of the table(ORGANIZATION EXTERNALexternal_table_clause).

• Within the table_properties clause, you canspecify only the parallel_clause. Theparallel_clause lets youparallelize subsequent queries on the external data and subsequentoperations that populate the external table.

• You can populate the external table at create time by using theAS subquery clause.

No other clauses are permitted in the same CREATETABLE statement.

See Also:

• "External Table Example"

• ALTER TABLE ... "PROJECT COLUMN Clause" for information on the effect ofchanging the default property of the column projection

Restrictions onExternal Tables External tables aresubject to the following restrictions:

• An external table cannot be a temporary table.

• You cannot specify constraints on an external table.

• An external table cannot have object type, varray, orLONG columns. However, you can populate LOB columns ofan external table with varray or LONG data from aninternal database table.

TYPE TYPE access_driver_type indicates theaccess driver of the external table. The access driveris the API that interprets the external data for the database.Oracle Database provides two access drivers:ORACLE_LOADER and ORACLE_DATAPUMP. If youdo not specify TYPE, then the database usesORACLE_LOADER as the default access driver. You mustspecify the ORACLE_DATAPUMP access driver if youspecify the AS subquery clause to unload datafrom one Oracle Database and reload it into the same or a differentOracle Database.

See Also:

Oracle Database Utilities for information about the ORACLE_LOADER and ORACLE_DATAPUMP access drivers

 

DEFAULTDIRECTORY DEFAULT DIRECTORY letsyou specify a default directory object corresponding to a directoryon the file system where the external data sources may reside. Thedefault directory can also be used by the access driver to storeauxiliary files such as error logs.

ACCESSPARAMETERS The optional ACCESSPARAMETERS clause lets you assign values to theparameters of the specific access driver for this externaltable.

• The opaque_format_spec lets you listthe parameters and their values. Please refer to OracleDatabase Utilities for information on how to specifyvalues for the opaque_format_spec.

  Field names specified in the opaque_format_spec must matchcolumns in the table definition. Oracle Database ignores any fieldin the opaque_format_spec that is notmatched by a column in the table definition.

• USING CLOB subquery lets you derive theparameters and their values through a subquery. The subquery cannotcontain any set operators or an ORDER BYclause. It must return one row containing a single item of datatypeCLOB.

Whether you specify the parameters in an opaque_format_spec or derivethem using a subquery, the database does not interpret anything inthis clause. It is up to the access driver to interpret thisinformation in the context of the external data.

 

LOCATIONThe LOCATION clause lets you specify one or moreexternal data sources. Usually the location_specifier is a file,but it need not be. Oracle Database does not interpret this clause.It is up to the access driver to interpret this information in thecontext of the external data. You cannot use wildcards in thelocation_specifier to specifymultiple files.

 

REJECTLIMIT The REJECT LIMITclause lets you specify how many conversion errors can occur duringa query of the external data before an Oracle Database error isreturned and the query is aborted. Thedefault value is 0.

 

 

 

 

官方文档地址：

http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref783

 

External Tables

External tables access data in external sources as if it were ina table in the database. You can connect to the database and createmetadata for the external table using DDL. The DDL for an externaltable consists of two parts: one part that describes the Oraclecolumn types, and another part (the access parameters) thatdescribes the mapping of the external data to the Oracle datacolumns.

An external table does not describe any data that is stored inthe database. Nor does it describe how data is stored in theexternal source. Instead, it describes how the external table layerneeds to present the data to the server. It is the responsibilityof the access driver and the external table layer to do thenecessary transformations required on the data in the datafile sothat it matches the external table definition.

 

External tables are read only;therefore, no DML operations are possible, and no index can becreated on them.--外部表是只读的，不能执行DML语句，在上边建索引和约束是不被允许的。

The Access Driver

When you create an external table,you specify its type. Each type of external table has its ownaccess driver that provides access parameters unique to that typeof external table. The access driver ensures that data from thedata source is processed so that it matches the definition of theexternal table. --外i部表的两种访问驱动功能是不相同的。

 

In the context of external tables, loading data refersto the act of reading data from an external table and loading itinto a table in the database. Unloading data refers to the act ofreading data from a table in the database and inserting it into anexternal table.

 

 

The default type for externaltables is ORACLE_LOADER, which lets you read tabledata from an external table and load it into a database.---ORACLE_LOADER类型。此种类型的驱动是让你从外部表里头读取数据然后加载进数据库里头。见下边例子

 

Oracle also provides theORACLE_DATAPUMP type, which lets you unload data (thatis, read data from a table in the database and insert it into anexternal table) and then reload it into an Oracledatabase.--两种方式的区别的描述。 ORACLE_DATAPUMP类型。此种类型的驱动是让你卸载数据，既是，从数据库表中读取数据然后插入到外部表里头，然后再加载进数据库表。

见下边例子

 

 

The definition of an external table is kept separatelyfrom the description of the data in the data source. This meansthat:--外部表的定义(元数据)和在数据源里头的数据定义是相互独立的。

• The source file can contain more or fewer fields thanthere are columns in the external table

• The datatypes for fields in the data source can bedifferent from the columns in the external table

Data Loading withExternal Tables

The main use for external tablesis to use them as a row source for loading data into an actualtable in the database. After you create an external table, you canthen use a CREATE TABLE ASSELECT or INSERT INTO ...AS SELECT statement, using the externaltable as the source of the SELECTclause.--可以通过上述语句来将数据转移到别的地方去。

Note:

You cannot insert data into external tables or update records inthem; external tables are read only.

When you access the external table through a SQL statement, thefields of the external table can be used just like any other fieldin a regular table. In particular, you can use the fields asarguments for any SQL built-in function, PL/SQL function, or Javafunction. This lets you manipulate data from the external source.For data warehousing, you can do more sophisticated transformationsin this way than you can with simple datatype conversions. You canalso use this mechanism in data warehousing to do datacleansing.

While external tables cannot contain a column object,constructor functions can be used to build a column object fromattributes in the external table

 

下是实际例子：--来源网上

1、Oracle10g新特性：利用外部表卸载数据  

  今天在看Oracle的CONCEPT文档时发现，Oracle的外部表也可以用来卸载数据了。

　　从10.2中开始，Oracle增加了外部表的一个新的访问驱动：ORACLE_DATAPUMP。而再次之前，只有一个默认的驱动ORACLE_LOADER。

　　使用ORACLE_DATAPUMP驱动，带来的一个优点是，可以利用外部表将数据库中的数据卸载到磁盘文件中，而这是10.1及以前版本所无法做到的。

下面看一个最简单的例子：

SQL> CREATE TABLE T_EXTERNAL
　　2 (
　　3 ID,
　　4 NAME
　　5 )
　　6 ORGANIZATION EXTERNAL
　　7 (
　　8 TYPE ORACLE_DATAPUMP
　　9 DEFAULT DIRECTORY D_OUTPUT
　　10 LOCATION('TEST.DMP')
　　11 )
　　12 AS SELECT ROWNUM RN, TNAME
　　13 FROM TAB;

 

 表已创建。
　　在这张外部表创建的同时，在D_OUTPUT所指向的操作系统目录下，生成了一个TEST.DMP二进制文件。
　　显示这个二进制文件中的可显示字符：

$ strings TEST.DMP 
　　IBMPC/WIN_NT-8.1.0 
　　ZHS16GBK 
　　LBB EMB GHC JWD SD EBE WMF DDG JG SJH SRH JGK CL EGM BJM RAP RLP RP KR PAR MS MRS JLS CET HLT 
　　10.02.00.01.00 
　　T 
　　1 
　　0 
　　3 
　　0 
　　ZHS16GBK 
　　AL16UTF16 
　　+00:00 
　　YANGTK 
　　T_EXTERNAL 
　　1 
　　0 
　　ID 
　　2 
　　22 
　　0 
　　-127 
　　0 
　　0 
　　0 
　　2 
　　0 
　　NAME 
　　1 
　　30 
　　0 
　　0 
　　852 
　　1 
　　30 
　　T_LOG< 
　　TEST_LOB< 
　　T_COMPRESS< 
　　T_TEMP2< 
　　T_RECORD< 
　　T_SESSION_STAT< 
　　T_TEMP< 
　　T_NO_EXISTS_BEFORE< 
　　T_CHAR< 
　　SYS_EXPORT_SCHEMA_01< 
　　T_EXTERNAL< 
　　T_LOAD_SPACE< 
　　T_LEVEL< 
　　T_LEVEL1< 
　　CHAINED_ROWS< 
　　T_TREE　　

 这个二进制文件可以用于在其他数据库中创建外部表：

SQL> CREATE TABLE T_EXTERNAL
　　2 (
　　3 ID NUMBER,
　　4 NAME VARCHAR2(30)
　　5 )
　　6 ORGANIZATION EXTERNAL
　　7 (
　　8 TYPE ORACLE_DATAPUMP
　　9 DEFAULT DIRECTORY D_OUTPUT
　　10 LOCATION('TEST.DMP')
　　11 );

表已创建。

SQL> SELECT * FROM T_EXTERNAL;
　　ID NAME
　　---------- ------------------------------
　　1 T_LOG
　　2 TEST_LOB
　　3 T_COMPRESS
　　4 T_TEMP2
　　5 T_RECORD
　　6 T_SESSION_STAT
　　7 T_TEMP
　　8 T
　　9 T_NO_EXISTS_BEFORE
　　10 T_CHAR
　　11 SYS_EXPORT_SCHEMA_01
　　12 T_EXTERNAL
　　13 T_LOAD_SPACE
　　14 T_LEVEL
　　15 T_LEVEL1
　　16 CHAINED_ROWS
　　17 T_TREE

已选择17行。

　　唯一可惜的是Oracle只能将数据写为二进制格式，因此可以考虑使用这种方法进行数据迁移或发布。不过想利用这种方法来生成文本报表也是行不通的。

　　最后提一下，外部表是只读表，可以利用外部表卸载数据，不过必须在建表时完成，一旦表建立成功，外部表就变成不可修改了。

 

SQL> INSERT INTO T_EXTERNAL VALUES (100, 'A');
　INSERT INTO T_EXTERNAL VALUES (100, 'A')

SQL> INSERT INTO T_EXTERNAL VALUES (100, 'A'); 　INSERT INTO T_EXTERNAL VALUES (100, 'A')

　　*第 1 行出现错误:

　　ORA-30657: 操作在外部组织表上不受支持

　　SQL> DROP TABLE T_EXTERNAL;

　　表已删除。

 

SQL> CREATE TABLE T_EXTERNAL
　　2 (
　　3 ID NUMBER,
　　4 NAME VARCHAR2(30)
　　5 )
　　6 ORGANIZATION EXTERNAL
　　7 (
　　8 TYPE ORACLE_DATAPUMP
　　9 DEFAULT DIRECTORY D_OUTPUT
　　10 LOCATION('TEST1.DMP')
　　11 );

SQL> CREATE TABLE T_EXTERNAL 　　2 ( 　　3 ID NUMBER, 　　4 NAME VARCHAR2(30) 　　5 ) 　　6 ORGANIZATION EXTERNAL 　　7 ( 　　8 TYPE ORACLE_DATAPUMP 　　9 DEFAULT DIRECTORY D_OUTPUT 　　10 LOCATION('TEST1.DMP') 　　11 );

　  表已创建。

 

SQL> INSERT INTO T_EXTERNAL VALUES (1, 'A');
INSERT INTO T_EXTERNAL VALUES (1, 'A')

SQL> INSERT INTO T_EXTERNAL VALUES (1, 'A'); INSERT INTO T_EXTERNAL VALUES (1, 'A')

　　*第 1 行出现错误:

　　ORA-30657: 操作在外部组织表上不受支持

 

 

2、Oracle为外部表提供了两种访问的驱动程序，默认的访问程序是oracle_loader,它允许使用oracle的装载技术从外部文件中读取数据。另一种访问驱动程序是oracle_datapump它从数据库读取数据库并将数据插入到外部表中。

1． Oracle_loader驱动程序

创建外部表去访问D:\waibubiao 下exp.dat文件中的记录

(1).创建一个目录

create directorymulu as 'D:\waibubiao'

SQL> create directorymulu as 'D:\waibubiao'；

目录已创建。

（2）.给scott用赋予对目录mulu的读写权限

SQL>grant read,write on directory mulu to scott;

授权成功。

SQL>

（3）.在soctt用户下创建外部表admin_ext_testing

create tableadmin_ext_testing

(idnumber, name varchar(10))

organizationexternal

(typeoracle_loader

default directorymulu

accessparameters

(

records delimitedby newline

badfilemulu:'exp.bad'

logfilemulu:'emp.log'

fields terminatedby ','

missing fieldvalues are null

(id,name))

location('exp.dat'))

reject limitunlimited;

SQL>conn scott/tiger

已连接。

SQL>create table admin_ext_testing

 2  (id number, name varchar(10))

 3  organization external

 4  (type oracle_loader

 5  default directory mulu

 6  access parameters

 7  (

 8  records delimited by newline

 9  badfile mulu:'exp.bad'

 10 logfile mulu:'emp.log'

 11 fields terminated by ','

 12 missing field values are null

 13 (id,name))

 14 location ('exp.dat'))

 15 reject limit unlimited;

表已创建。

SQL>

（4）.外部表创建成功，查询外部表中的数据

SQL>select * from admin_ext_testing;

       ID NAME

------------------------------

        1 jack

        2 pter

        3 tom

        4 sola

        5 kobe

        6 ddd

        7 jjjjj

        8 999

已选择8行。

SQL>

至此oracle可以访问外部文件的数据了

 

2． Oracle_datapump驱动程序

创建一个外部表去访问另一个数据库中的数据库，先把令一个数据库中的数据导入一个文件，然后再去访问外部文件。

数据库mdsp要访问数据库wlm中一个表test的数据，数据如下：

SQL>select * from test;

       ID NAME

------------------------------

     1000 abc

     1001 abc

     1002 abcer

     1003 defalut

SQL>

（1）.在wlm数据库上创建一个目录data

create directorymulu as 'D:\data'

SQL>create directory mulu as 'D:\data';

目录已创建。

SQL>

（2）在wlm数据库上创建外部表，执行完下面的语句后，会把表test中数据放到d:\data下TEST.DAT中

SQL>create table ext_test (id , name)

 2  organization external

 3  (

 4   typeoracle_datapump

 5   default directorymulu

 6  location('test.dat')

 7   )

 8   as select * fromtest;

表已创建。

SQL>

（3）在mdsp数据库中创建目录data指向d:\data

SQL>create directory data as 'D:\data'

目录已创建。

SQL>

（4）在mdsp上创建外部表去访问TEST.DAT文件中的数据

SQL>create table ext_mdsp_test (id number , namevarchar(10))

 2  organization external

 3  (

 4   typeoracle_datapump

 5   default directorydata

 6  location('test.dat')

 7   );

表已创建。

SQL>

（5）在mdsp数据库上访问外部表ext_mdsp_test

SQL>select * from ext_mdsp_test;

       ID NAME

------------------------------

     1000 abc

     1001 abc

     1002 abcer

     1003defalut

SQL>

注：在外部表上不能执行DML操作