ITPub博客

首页 > Linux操作系统 > Linux操作系统 > External Table外部表

External Table外部表

原创 Linux操作系统 作者:jiuniang012 时间:2009-09-07 11:31:48 0 删除 编辑
External Table外部表

1. 概念
    外部表是存储于数据库外的一种表,它允许以只读的形式读取外部表里的数据. 你可以像普通表一样对外部表进行SELECT, SORT, JOIN操作,也可以在外部表上建视图,同义词等,但不能有DML操作.

   外部表的定义也就像创建一个视图一样,查询外部表的数据不用先把数据装载进外部表里.对外部表的访问主要是通过ACCESS DRIVER来实现的.

Access Driver:
Oracle 提供两种访问驱动: ORACLE_LOADER, ORACLE_DATAPUMP
ORACLE_LOADER: 它利用oracle loader技术从外部表读取数据. 它还具有类似SQL*Loader工具控制文件语法的数据映射能力.

ORACLE_DATAPUMP: 它提供卸载数据的能力,即把数据从数据库里导入一个外部表,再由一个或几个外部表导入到数据库里. 对于AS subquery的子句必须使用ORACLE_DATAPUMP.

创建外部表实例:

The file empxt1.dat contains the following sample data:

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

The file empxt2.dat contains the following sample data:

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

The following hypothetical SQL statements create an external table in the hr schema named admin_ext_employees and load its data into the hr.employees table.

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;

Snap1.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9466564/viewspace-614096/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 索引 - 基础篇
请登录后发表评论 登录
全部评论

注册时间:2009-07-02

  • 博文量
    126
  • 访问量
    211290