Oracle 外部表

Oracle数据库允许对外部表数据进行只读访问。外部表定义为不驻留在数据库中的表，并且可以是为其提供了访问驱动程序的任何格式。通过为数据库提供描述外部表的元数据，数据库能够公开外部表中的数据，就好像它是驻留在常规数据库表中的数据一样。可以使用SQL直接和并行查询外部数据。

例如，您可以选择，连接或排序外部表数据。您还可以为外部表创建视图和同义词。但是，在外部表上不能进行DML操作（UPDATE，INSERT或DELETE），也不能创建索引。

外部表还提供了一个框架，用于将任意SELECT语句的结果卸载到Oracle Data Pump可以使用的独立于平台的Oracle专有格式中。

通过CREATE TABLE...ORGANIZATION EXTERNAL语句定义外部表的元数据。可以将此外部表定义视为允许对外部数据运行任何SQL查询而无需首先将外部数据加载到数据库中的视图。访问驱动程序是用于读取表中外部数据的实际机制。使用外部表卸载数据时，将根据SELECT语句中的数据类型自动创建元数据。

Oracle数据库为外部表提供了两个访问驱动程序。默认访问驱动程序是ORACLE_LOADER，允许使用Oracle加载器技术从外部文件读取数据。ORACLE_LOADER访问驱动器提供的数据映射功能是SQL * Loader的控制文件语法的子集。第二个访问驱动程序ORACLE_DATAPUMP允许您卸载数据 - 即从数据库读取数据并将其插入由一个或多个外部文件表示的外部表中 - 然后将其重新加载到Oracle数据库中。

Oracle数据库外部表功能为执行数据仓库常用的基本提取，转换和加载（ExtractionTransformationLoading）任务提供了有价值的方法。

以下是外部表仅支持的DDL语句，并且不支持这些语句的所有子句。 

创建外部表

创造外部表使用CREATE TABLE的ORGANIZATION EXTERNAL子句。你实际上并没有创造一张表；也就是说，外部表没有任何与之关联的区。而是在数据字典中创建元数据，使您可以访问外部数据。

注意：外部表不能包含虚拟列。

以下示例创建外部表，然后将数据加载到数据库表。或者，通过指定CREATE TABLE语句的AS subquery子句来通过外部表框架卸载数据。外部表数据泵卸载只能使用ORACLE_DATAPUMP访问驱动程序。

示例：创建外部表并加载数据

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

 

以下假设的SQL语句在hr模式中创建外部表admin_ext_employees，并将其数据加载到hr.employees表中。

CONNECT  /  AS SYSDBA;
-- Set up directories and grant access to hr 
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/flatfiles/data'; 
CREATE OR REPLACE DIRECTORY admin_log_dir 
    AS '/flatfiles/log'; 
CREATE OR REPLACE DIRECTORY admin_bad_dir 
    AS '/flatfiles/bad'; 
GRANT READ ON DIRECTORY admin_dat_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_log_dir TO hr; 
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects. Provide the user password (hr) when prompted.
CONNECT hr
-- create the external table
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 admin_dat_dir 
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'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; 
-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;
-- load the data in hr employees table
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
                       hire_date, salary, commission_pct, department_id, email) 
            SELECT * FROM admin_ext_employees;

 

此示例中的前几个语句为包含数据源的操作系统目录以及访问参数中指定的错误记录和日志文件创建目录对象。您还必须根据需要授予目录对象READ或WRITE权限。

注意：创建目录对象或BFILE时，请确保满足以下条件：

• 操作系统文件不能是符号链接或硬链接。

• Oracle数据库目录对象中指定的操作系统目录路径必须是现有的OS目录路径。

• 目录对象中指定的操作系统目录路径不应在其组件中包含任何符号链接。

TYPE声明外部表的访问驱动程序。访问驱动程序是解释数据库外部数据的API。Oracle数据库提供了两个访问驱动程序：ORACLE_LOADER和ORACLE_DATAPUMP。如果省略TYPE声明，ORACLE_LOADER则是默认访问驱动程序。如果指定As subquery子句从一个Oracle数据库卸载数据，并将其重新加载到相同或不同的Oracle数据库中，则必须指定ORACLE_DATAPUMP访问驱动程序。

ACCESS PARAMETERS子句中指定的访问参数对数据库是不透明的。这些访问参数由访问驱动程序定义，并在访问外部表时由数据库提供给访问驱动程序。有关访问参数的说明，请参见Oracle数据库实用程序ORACLE_LOADER。

PARALLEL子句支持对数据源进行并行查询。默认情况下，并行性粒度是数据源，但只要有可能，就会实现数据源中的并行访问。例如，如果PARALLEL=3已指定，则多个并行执行服务器可能正在处理数据源。但是，只有满足以下所有条件时，访问驱动程序才会提供数据源中的并行访问：

• 介质允许在数据源中随机定位

• 可以从随机位置找到记录边界

• 数据文件足够大，可以分成多个块

注意：仅在处理大量数据时才 指定PARALLEL子句。否则，不建议指定一个子句，这样做可能是有害的。

REJECT LIMIT子句指定在查询外部数据期间可能发生的错误数量没有限制。对于并行访问，此限制适用于每个并行执行服务器。例如，如果REJECT LIMIT指定，则每个并行查询过程允许10次拒绝。因此，REJECT LIMIT并行查询的唯一精确强制值是0和UNLIMITED。

INSERT INTO TABLE语句生成从外部数据源到Oracle数据库SQL引擎的数据流，在该数据流中处理数据。当访问驱动程序从外部表源解析数据并将其提供给外部表接口时，外部数据将从其外部表示转换为其Oracle数据库内部数据类型。

修改外部表

你可以使用任何一个下表中的ALTER TABLE子句修改外部表。不允许其他子句。

Clause	Description	Example
REJECT LIMIT	修改reject limit	ALTER TABLE admin_ext_employees REJECT LIMIT 100;
PROJECT COLUMN	确定访问驱动程序如何验证后续查询中的行： PROJECT COLUMN REFERENCED: 访问驱动程序仅处理查询的选择列表中的列。从同一外部表查询不同的列列表时，此设置可能无法提供一致的行集。这是默认值。 PROJECT COLUMN ALL: 访问驱动程序处理外部表上定义的所有列。查询外部表时，此设置始终提供一致的行集。	ALTER TABLE admin_ext_employees PROJECT COLUMN REFERNCED; ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;
DEFAULT DIRECTORY	更改默认目录声明	ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERS	允许更改访问参数，而无需删除和重新创建外部表元数据	ALTER TABLE admin_ext_employees ACCESS PARAMETERS (FIELDS TERMINATED BY ';');
LOCATION	允许更改数据源，而无需删除和重新创建外部表元数据	ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');
PARALLEL	与常规表没有区别。允许更改并行度。
ADD COLUMN	与常规表没有区别。允许将列添加到外部表。不允许使用虚拟列。
MODIFY COLUMN	与常规表没有区别。允许修改外部表列。不允许使用虚拟列。
SET UNUSED	透明地转换成ALTER TABLE DROP COLUMN命令。由于外部表仅包含数据库中的元数据，因此该DROP COLUMN命令与该SET UNUSED命令等效执行。
DROP COLUMN	与常规表没有区别。允许删除外部表列。
RENAME TO	与常规表没有区别。允许重命名外部表。

 

删除外部表

DROP TABLE语句仅删除数据库中的表元数据。它对驻留在数据库之外的实际数据没有影响。

外部表的系统和对象权限

外部表的系统和对象权限是常规表的权限的子集。只有以下系统权限适用于外部表：

• CREATE ANY TABLE

• ALTER ANY TABLE

• DROP ANY TABLE

• SELECT ANY TABLE

只有以下对象权限适用于外部表：

• ALTER

• SELECT

但是，与目录关联的对象权限是：

• READ

• WRITE

对于外部表，READ对包含数据源WRITE的目录对象需要特权，而包含bad，日志或丢弃文件的目录对象需要特权。