Oracle 外部表

一. 官网对外部表的说明

Managing External Tables

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896

Oracle Database allows you read-only access to data in external tables. 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. By providing the database with metadata describing an external table, the database is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.

You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.

External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump. External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.

--Oracle从9i开始支持外部表，外部表的一系列增强是将其设计为ETL的增强工具，并且可以取代部分sqlldr的功能。常规的数据库应用不会常用这个的。

The means of defining the metadata for external tables is through the CREATE TABLE...ORGANIZATION EXTERNAL statement. This external table definition can be thought of as a view that allows running any SQL query against external data without requiring that the external data first be loaded into the database. An access driver is the actual mechanism used to read the external data in the table. When you use external tables to unload data, the metadata is automatically created based on the datatypes in the SELECT statement.

External Table Access Drivers

An access driver is an API that interprets the external data for the database. The access driver runs inside the database, which uses the driver to read the data in the external table. The access driver and the external table layer are responsible for performing the transformations required on the data in the data file so that it matches the external table definition.

Oracle Database provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.

The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.

External Table Restrictions

The following are restrictions on external tables:

（1）The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.

（2）Virtual columns are not supported

二. 外部表示例

2.1 创建目录并授权

C:/Users/Administrator.DavidDai>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 12月 28 23:39:31 2010

SQL> conn / as sysdba;

已连接。

SQL> CREATE OR REPLACE DIRECTORY Extdump AS 'D:/Backup';

目录已创建。

SQL> GRANT READ,WRITE ON DIRECTORY Extdump TO SYSTEM;

授权成功。

SQL>

2.2. 创建数据文件

在D:/backup 目录下创建2个数据文件，内容如下：

empxt1.dat 内容如下：

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

empxt2.dat内容如下：

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

2.3 创建外部表

CREATE TABLE admin_ext_employees

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY Extdump

ACCESS PARAMETERS

(

records delimited by newline

badfile Extdump:'empxt%a_%p.bad'

logfile Extdump:'empxt%a_%p.log'

fields terminated by ','

missing field values are null

( employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

LOCATION ('empxt1.dat', 'empxt2.dat')

)

PARALLEL

REJECT LIMIT UNLIMITED;

启动并行（good if lots of data to load）：

SQL> ALTER SESSION ENABLE PARALLEL DML;

会话已更改

查询外部表的数据：

SQL> select employee_id,first_name,last_name,job_id from admin_ext_employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID

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

360 Jane Janus ST_CLERK

361 Mark Jasper SA_REP

362 Brenda Starr AD_ASST

363 Alex Alda AC_MGR

401 Jesse Cromwell HR_REP

402 Abby Applegate IT_PROG

403 Carol Cousins AD_VP

404 John Richardson AC_ACCOUNT

已选择8行。

2.4 修改外部表

参考下表：

ALTER TABLE Clause	Description	Example
REJECT LIMIT	Changes the reject limit	ALTER TABLE admin_ext_employees REJECT LIMIT 100;
PROJECT COLUMN	Determines how the access driver validates rows in subsequent queries: （1）PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. （2）PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table. This is the default.	ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED; ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;
DEFAULT DIRECTORY	Changes the default directory specification	ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERS	Allows access parameters to be changed without dropping and re-creating the external table metadata	ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');
LOCATION	Allows data sources to be changed without dropping and re-creating the external table metadata	ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');
PARALLEL	No difference from regular tables. Allows degree of parallelism to be changed.	No new syntax
ADD COLUMN	No difference from regular tables. Allows a column to be added to an external table. Virtual columns are not permitted.	No new syntax
MODIFY COLUMN	No difference from regular tables. Allows an external table column to be modified. Virtual columns are not permitted.	No new syntax
SET UNUSED	Transparently converted into an ALTER TABLE DROP COLUMN command. Because external tables consist of metadata only in the database, the DROP COLUMN command performs equivalently to the SET UNUSED command.	No new syntax
DROP COLUMN	No difference from regular tables. Allows an external table column to be dropped.	No new syntax
RENAME TO	No difference from regular tables. Allows external table to be renamed.	No new syntax

2.5 和外部表相关的权限问题

（1） External Table上可用的系统权限（System Privileges）

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

SELECT ANY TABLE

（2）External Table 上可用的对象权限（Object Privileges）

ALTER

SELECT

（3）目录权限

READ

WRITE

2.6 删除外部表

可以使用DROP TABLE 删除外部表在数据库中的对象定义（metadata）。对真实存在的数据文件没有影响。

SQL> drop table ADMIN_EXT_EMPLOYEES;

表已删除

删除外部表和目录之外，还需要注意点，就是如果目录里有多个外部表的话，要确定把相应的关系都断掉在删除目录。可以通过如下SQL 来查看外部表和目录之间的关系：

SQL> select * from dba_external_locations;

OWNER TABLE_NAME LOCATION

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

SH SALES_TRANSACTIONS_EXT sale1v3.dat

SYS ADMIN_EXT_EMPLOYEES empxt1.dat

SYS ADMIN_EXT_EMPLOYEES empxt2.dat

三. 使用外部表查询警告日志文件

3.1 创建目录，指定到alert log 目录

SQL> create or replace directory bdump as 'D:/app/Administrator/diag/rdbms/orcl/orcl/trace';

目录已创建。

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BDUMP D:/app/Administrator/diag/rdbms/orcl/orcl/trace

SYS EXTDUMP D:/Backup

SYS DUMP d:/Backup

SYS SUBDIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYS SS_OE_XMLDIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYS LOG_FILE_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYS DATA_FILE_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYS XMLDIR c:/ade/aime_dadvfm0254/oracle/rdbms/xml

SYS MEDIA_DIR D:/app/Administrator/product/11.2.0/dbhome_1/demo/

SYS DATA_PUMP_DIR D:/app/Administrator/admin/orcl/dpdump/

SYS ORACLE_OCM_CONFIG_DIR D:/app/Administrator/product/11.2.0/dbhome_1/ccr/s

已选择11行。

3.2 创建外部表

SQL> create table alert_log ( text varchar2(400) )

2 organization external (

3 type oracle_loader

4 default directory BDUMP

5 access parameters (

6 records delimited by newline

7 nobadfile

8 nodiscardfile

9 nologfile)

10 location('alert_orcl.log'))

11 reject limit unlimited;

表已创建。

3.3 查看alert log 表

SQL> select * from alert_log where rownum<10;

TEXT

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

Tue Nov 02 16:54:23 2010

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instance monitoring created

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

已选择9行。

SQL> select * from alert_log where text like 'ORA-%';

TEXT

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

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'D:/APP/ADMINISTRATOR/ORADATA/ORCL/REDO01.LOG'