外部表使用方式包括数据泵卸载数据，数据泵加载数据和oracle_loader加载数据。

首先，需要创建一个Oracle目录

SQL> create directory tmp as 'c:\tmp';
目录已创建。

1.数据泵卸载数据
create table t1
(
        object_name,object_type,created
)
organization external
(
        type oracle_datapump
        default directory tmp
        location('t1.exp')
)
parallel 2
as
select object_name,object_type,created from dba_objects;

在目录tmp下，可以看到卸载的数据文件t1.exp，拷贝t1.exp为t2.exp

2.数据泵加载数据
使用外部表加载t2.exp文件。
create table t2
(
        object_name varchar2(50),
        object_type varchar2(50),
        created date
)
organization external
(
        type oracle_datapump
        default directory tmp
        location('t2.exp')
)
parallel 2
;

3.使用oracle_loader加载数据
在Oracle目录tmp下创建文件test.dat，内容为
tom,70,2013-06-01
Ada,90,2013-06-01

使用外部表加载这个文件
create table t3
(
        name varchar2(20),
        grade integer,
        created date
)
organization external
(
        type oracle_loader
        default directory tmp
        access parameters
        (
                records delimited by newline
                FIELDS TERMINATED BY "," LDRTRIM
        REJECT ROWS WITH ALL NULL FIELDS
                (
                        name CHAR(255) TERMINATED BY ",",
            grade CHAR(255) TERMINATED BY ",",
            created date 'yyyy-mm-dd' TERMINATED BY ","
                )
        )
        location ('test.dat')
);

http://blog.csdn.net/leshami/article/details/6078481

ITPub博客

Oracle外部表

最新文章