当前位置:首页 > 开发 > 数据库 > 正文

oracle 外部表

发表于: 2012-10-12   作者:daizj   来源:转载   浏览:
摘要:     oracle外部表是只允许只读访问,不能进行DML操作,不能创建索引,可以对外部表进行的查询,连接,排序,创建视图和创建同义词操作。 you can select, join, or sort external table data. You can also create views and synonyms for external tables. Ho
    oracle外部表是只允许只读访问,不能进行DML操作,不能创建索引,可以对外部表进行的查询,连接,排序,创建视图和创建同义词操作。
you can 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.

    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.

EXAMPLE: Creating an External Table and Loading Data

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
CONNECT hr/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;

上面REJECT LIMIT UNLIMITED 表示能接受的错误数不限制。默认是0个。

oracle 外部表

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
一. 官网对外部表的说明 Managing External Tables http://download.oracle.com/docs/cd/E11882_01/
一. 官网对外部表的说明 Managing External Tables http://download.oracle.com/docs/cd/E11882_01/
对外部表的理解及测试,,,,如有理解不正确请大家指正 语法: create table 表名( 列名1,列名2,.
1、创建目录(create any directory): SQL> create user Oracle identified by oracle; 用户已
三 外部表 官方文档:Administrator’s Guide -> 15 Managing Tables -> Managing External T
create'hivehbaseStu', 'stu' put'hivehbaseStu', 'row1', 'stu:stuName', 'tom' put'hivehbaseStu'
create'hivehbaseStu', 'stu' put'hivehbaseStu', 'row1', 'stu:stuName', 'tom' put'hivehbaseStu'
1:分区表 图:创建分区表 图:导入数据 图:在HDFS的显示信息 分区字段就是一个文件夹的标识 图:
前一段时间了解到的,今天有空写下,备忘~ 先来说说横表和纵坐表的概念,先来看看以下两图: 第一张
前一段时间了解到的,今天有空写下,备忘~ 先来说说横表和纵坐表的概念,先来看看以下两图: 第一张
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号