管理外部表

  • Hoegh

    2015-05-08 22:59:40
  • Oracle
  • 原创

关于外部表

Oracle数据库允许你以只读方式访问外部表的数据。外部表被定义为不驻留在数据库的表，而且，只要提供了访问驱动程序外部表支持任意格式。只要提供了描述外部表的元数据，Oracle数据库就可以展现外部表中的数据，就像它是驻留在一个常规的数据库表一样。使用SQL可以直接并行查询外部表。


对于外部表，你可以执行查询操作，连接操作或者对数据进行排序。你也可以给外部表创建视图或者同义词。但是，外部表不支持DML操作（更新、插入或者删除），也不能创建索引。

 

外部表提供了一个框架，我们可以将任意SELECT语句返回的结果转化为独立于平台的Oracle专有的格式，Oracle数据泵可以使用这种格式的文件。外部表为执行基本提取、转换和加载（ETL）任务提供了一个重要的手段，这在数据仓库中是很常见的。

 

定义外部表元数据是通过CREATE TABLE...ORGANIZATION EXTERNAL来完成。这个外部表定义可以视为一个视图，可以执行任意的SQL查询，而无需把外部表数据加载到数据库。访问驱动程序是用来把外部表数据读取到表的实际机制。当你使用外部表卸载数据时，Oracle会根据SELECT语句中的数据类型自动创建元数据。

 

Oracle数据库提供了两种外部表访问驱动程序。默认的访问驱动程序是ORACLE_LOADER，它使用Oracle加载技术从外部表中读取数据。ORACLE_LOADER提供了数据映射功能，这是SQL*Loader实用程序控制文件语法的一个子集。第二个访问驱动程序是ORACLE_DATAPUMP，它是从数据库中读取数据并插入到外部表中，保存为一个或多个外部文件，然后重新加载到Oracle数据库。

 

注意：

外部表不支持使用ANALYZE语句来手机统计信息，可以使用DBMS_STATS包来代替；

外部表不支持虚列；

 

创建外部表

你可以通过CREATE TABLE...ORGANIZATION EXTERNAL语句来创建外部表，该语句仅在数据字典中创建元数据。

 

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

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

在这个示例中，外部表的数据保存在两个文本文件，empxt1.dat和empxt2.dat。

 文件empxt1.dat包含以下数据：

 

点击(此处)折叠或打开

  1. 360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
  2. 361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
  3. 362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
  4. 363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

文件empxt2.dat包含以下数据：


点击(此处)折叠或打开

  1. 401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
  2. 402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
  3. 403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
  4. 404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

 

下面的SQL语句在HR模式下，创建了一个名为admin_ext_employees的外部表，并将外部表的数据加载到hr.employees表中。

 以下的段落包含了上述示例的描述信息。

 

点击(此处)折叠或打开

  1. CONNECT / AS SYSDBA;
  2. -- Set up directories and grant access to hr
  3. CREATE OR REPLACE DIRECTORY admin_dat_dir
  4.     AS \'/flatfiles/data\';
  5. CREATE OR REPLACE DIRECTORY admin_log_dir
  6.     AS \'/flatfiles/log\';
  7. CREATE OR REPLACE DIRECTORY admin_bad_dir
  8.     AS \'/flatfiles/bad\';
  9. GRANT READ ON DIRECTORY admin_dat_dir TO hr;
  10. GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
  11. GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
  12. -- hr connects. Provide the user password (hr) when prompted.
  13. CONNECT hr
  14. -- create the external table
  15. CREATE TABLE admin_ext_employees
  16.                    (employee_id NUMBER(4),
  17.                     first_name VARCHAR2(20),
  18.                     last_name VARCHAR2(25),
  19.                     job_id VARCHAR2(10),
  20.                     manager_id NUMBER(4),
  21.                     hire_date DATE,
  22.                     salary NUMBER(8,2),
  23.                     commission_pct NUMBER(2,2),
  24.                     department_id NUMBER(4),
  25.                     email VARCHAR2(25)
  26.                    )
  27.      ORGANIZATION EXTERNAL
  28.      (
  29.        TYPE ORACLE_LOADER
  30.        DEFAULT DIRECTORY admin_dat_dir
  31.        ACCESS PARAMETERS
  32.        (
  33.          records delimited by newline
  34.          badfile admin_bad_dir:\'empxt%a_%p.bad\'
  35.          logfile admin_log_dir:\'empxt%a_%p.log\'
  36.          fields terminated by \',\'
  37.          missing field values are null
  38.          ( employee_id, first_name, last_name, job_id, manager_id,
  39.            hire_date char date_format date mask \"dd-mon-yyyy\",
  40.            salary, commission_pct, department_id, email
  41.          )
  42.        )
  43.        LOCATION (\'empxt1.dat\', \'empxt2.dat\')
  44.      )
  45.      PARALLEL
  46.      REJECT LIMIT UNLIMITED;
  47. -- enable parallel for loading (good if lots of data to load)
  48. ALTER SESSION ENABLE PARALLEL DML;
  49. -- load the data in hr employees table
  50. INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
  51.                        hire_date, salary, commission_pct, department_id, email)
  52.             SELECT * FROM admin_ext_employees;

示例最开始的几条语句创建了几个目录对象，包括包含数据源的操作系统目录，也包括输入参数需要用到的保存不良记录以及日志的目录。如果合适的话，你必须赋予读或者写目录对象权限。

 

注意：

当创建目录对象或者BFILEs时，必须确保满足下面几个条件：

操作系统文件不能是符号连接或者硬连接；

Oracle数据库目录对象中定义的操作系统目录路径必须存在；

Oracle数据库目录对象中定义的操作系统目录，在其组件中不应包含任何符号连接；

 

 Type参数用来指定外部表的访问驱动程序。访问驱动程序时用来为数据库解析外部数据。如果你忽略了Type类型说明，ORACLE_LOADER是默认的访问驱动程序。如果你指定AS子查询子句从一个Oracle数据库卸载数据，并重新加在到相同或不同的Oracle数据库，您必须指定ORACLE_DATAPUMP访问驱动程序。

 

在ACCESS PARAMETERS子句中制定的访问参数，对数据库时不透明的。这些访问参数由访问驱动程序定义，当访问外部表时需要提供给访问驱动程序。请参考Oracle实用程序对ORACLE_LOADER访问参数的描述。

 

并行子句允许对数据源进行并行查询。默认的并行的颗粒是数据源，但数据源内的并行访问只能尽可能地实现。例如，如果PARALLEL参数被指定为3，那么多个并行处理服务器可以处理一个数据源。但是，只有满足所有以下条件时，访问驱动程序才会提供并行访问：

  • 介质允许一个数据库内随机定位
  • 可能从随机位置中找到记录边界
  • 数据文件足够大，值得并行访问

 

注意：

只有当有大量的数据处理指定PARALLEL子句是有价值的。否则，这是不可取指定并行条款，而这样做可能是有害的。

 

REJECT LIMIT子句指定了在查询外部表期间，对可能发生地错误数量没有限制。对于并行访问，

REJECT LIMIT子句分别独立适用于每一个并行执行服务器。例如，如果REJECT LIMIT子句被指定为10，那么每个并行查询过程可以允许最多10个拒绝。因此，使用并行度为2和REJECT LIMIT为10，语句可能在10到20个拒绝之间会面临失败。如果一个并行服务器处理了所有10个拒绝，那么就达到了上限，语句将会终止。然而，一个并行处理服务器可以处理9个拒绝，另一个并行处理服务器也可以处理9个拒绝，那么这个语句虽然有18个拒绝，但是也会成功执行。因此，精确执行并行查询的REJECT LIMIT值只能是0和UNLIMITED。

 

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

修改外部表

 您可以使用下表所示的ALTER TABLE子句来改变外部表的特性，其他子句都是不允许的。

ALTER TABLE 子句

描述

举例

REJECT LIMIT

修改拒绝限制，默认值为0

ALTER TABLE admin_ext_employees REJECT LIMIT 100;

PROJECT COLUMN

决定访问驱动程序在随后的查询中如何验证数据行：

PROJECT COLUMN REFERENCED:访问驱动程序仅处理查询中选择的列。当从同一个外部表中查询不同列时，该设置可能无法提供一致的行集合。

PROJECT COLUMN ALL:访问驱动程序处理定义在外部表上的所有列。该设置当查询外部表时始终提供一致的行集合。这是默认设置。

ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED;

ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

DEFAULT DIRECTORY

修改指定的默认目录

ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

 

预处理外部表

警告：

在使用预处理子句时要考虑安全隐患。

外部表可以通过用户提供的预处理程序进行预处理。通过使用预处理程序，用户可以从驱动程序不支持的文件格式中使用数据。例如，用户可能希望访问存储在一个压缩格式的数据，通过指定一个解压缩程序用于ORACLE_LOADER访问驱动程序，这样就允许解压缩数据，进而驱动程序就能处理数据。

 

要使用预处理功能，在ORACLE_LOADER访问驱动程序的访问参数中，您必须指定PREPROCESSOR子句。预处理器必须是一个目录对象，并且访问外部表的用户必须具有目录对象的执行权限。下面的示例包括PREPROCESSOR子句并且指定了目录和预处理程序。

 

点击(此处)折叠或打开

  1. CREATE TABLE sales_transactions_ext
  2. (PROD_ID NUMBER,
  3.  CUST_ID NUMBER,
  4.  TIME_ID DATE,
  5.  CHANNEL_ID CHAR,
  6.  PROMO_ID NUMBER,
  7.  QUANTITY_SOLD NUMBER,
  8.  AMOUNT_SOLD NUMBER(10,2),
  9.  UNIT_COST NUMBER(10,2),
  10.  UNIT_PRICE NUMBER(10,2))
  11. ORGANIZATION external
  12. (TYPE oracle_loader
  13.  DEFAULT DIRECTORY data_file_dir
  14.  ACCESS PARAMETERS
  15.   (RECORDS DELIMITED BY NEWLINE
  16.    CHARACTERSET AL32UTF8
  17.    PREPROCESSOR exec_file_dir:\'zcat\'
  18.    BADFILE log_file_dir:\'sh_sales.bad_xt\'
  19.    LOGFILE log_file_dir:\'sh_sales.log_xt\'
  20.    FIELDS TERMINATED BY \"|\" LDRTRIM
  21.   ( PROD_ID,
  22.     CUST_ID,
  23.     TIME_ID,
  24.     CHANNEL_ID,
  25.     PROMO_ID,
  26.     QUANTITY_SOLD,
  27.     AMOUNT_SOLD,
  28.     UNIT_COST,
  29.     UNIT_PRICE))
  30.  location (\'sh_sales.dat.gz\')
  31. )REJECT LIMIT UNLIMITED;

PREPROCESSOR子句不适用于使用Oracle Database Vault的数据库。

 

删除外部表

对于外部表，DROP TABLE语句仅删除在数据库中的元数据。由于外部表驻留在数据库之外，因此外部表的实际数据不受影响。


外部表的系统和对象权限

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

CREATE ANY TABLE

ALTER ANY TABLE

DROP ANY TABLE

SELECT ANY TABLE

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

ALTER

SELECT

然而，在一个目录相关联的对象的权限是：

READ

WRITE

对于外部表，需要对包含数据源目录对象的读权限，对于包含不良数据、日志或者丢弃文件等目录对象则需要写权限。

 

请使用浏览器的分享功能分享到微信等