1、创建DIRECTORY，并权限授予给用户

create or replace directory dmp as '/OracleBackup/backup/';

grant read,write on directory dmp to scott;

2、把外部文件放到DIRECTORY目录下

注意权限问题

3、创建外部表

ORACLE_LOADER驱动方式

   create table test_ext_3 (c1  varchar2(20),

        c2 varchar2(50),

        c3 varchar2(50),

             c4 varchar2(50),

             c5 varchar2(50),

             c6 varchar2(50),

             c7 varchar2(50),

            c8 varchar2(50)                     )

        organization external  

        (

                type ORACLE_LOADER   

----默认驱动器类型ORACLE_LOADER，也可使用oracle_datapump    

                default directory dmp

                ACCESS PARAMETERS (

                        RECORDS DELIMITED BY NEWLINE  

---默认，一行一条记录

                        BADFILE 'DMP':'test.bad'         

----坏记录保存的文件，格式可以是DIRECTORY_NAME:文件名

                        LOGFILE 'test.log01'

                        FIELDS TERMINATED BY ":" LDRTRIM

                        MISSING FIELD VALUES ARE NULL

-----缺少字段值时，以空值代替

                        REJECT ROWS WITH ALL NULL FIELDS

----限制整行为空的数据

                )

                location('passwd')

        )

        reject limit unlimited;

oracle_datapump驱动方式

------先从源库中卸载数据到外部文件

-----卸载时支持压缩及加密

create table test_xt

organization external(

        type oracle_datapump

        default directory dmp

        location ('scott2.dmp')

)

as

select empno,ename,sal from scott.emp;

-----从之前卸载得到的dmp文件装载数据到数据库中

create table test_xt2(

        empno number,

        ename varchar2(20),

        sal number

)

organization external(

        type oracle_datapump

        default directory dmp

        location ('scott2.dmp')

);