外部表datapump方式的导出和导入与Oracle_loader的外部表

 

--windows下数据库操作  oracle_loader类型外部表

--数据文件   ldr_case1.ctl
LOAD DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

SQL> conn / as sysdba
已连接。
SQL> create directory extdir as 'd:\';

目录已创建。

SQL> grant read,write on directory extdir to zhaomn;

授权成功。

SQL> conn zhaomn/zhaomn
已连接。

SQL> create table ext_case1
  2  (ename varchar2(10),
  3  job varchar2(20),
  4  sal number)
  5  organization external
  6  (type oracle_loader
  7  default directory EXTDIR
  8  access parameters
  9  (records delimited by newline
 10  skip 6
 11  fields terminated by ","
 12  (ename,job,sal)
 13  )
 14  location('ldr_case1.ctl')
 15  );

表已创建。

SQL> select * from ext_case1;

ENAME      JOB                         SAL
---------- -------------------- ----------
SMITH      CLEAK                      3904
ALLEN      SALESMAN                   2891
WARD       SALESMAN                   3128
KING       PRESIDENT                  2523

 

--外部表类型为type oracle_loader

-------------------------------------------------------------------------------------------------------------------

--类型为type  oracle_datapump外部表

SQL>  create table externtest
  2     organization external
  3     ( type  oracle_datapump
  4      default directory mydir
  5      location ('extest.dmp')
  6     )
  7     as
  8     select e.ename,d.dname
  9     from dept d, emp e
 10   where d.deptno=e.deptno
 11  ;

Table created.

SQL> select * from externtest;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- --------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

 

--导入方式为，可直接将extest.dmp文件拷贝到另一个库，新建外部表

SQL>  create table ext
  2     ( ename varchar2 (20),
  3     dname varchar2 (20) )
  4     organization external
  5     (type  oracle_datapump
  6     default directory EXTDIR
  7    location ('extest.dmp'));

表已创建。

SQL> select * from ext;

ENAME                DNAME
-------------------- --------------------
SMITH                RESEARCH
ALLEN                SALES
WARD                 SALES
JONES                RESEARCH
MARTIN               SALES
BLAKE                SALES
CLARK                ACCOUNTING
SCOTT                RESEARCH
KING                 ACCOUNTING
TURNER               SALES
ADAMS                RESEARCH

ENAME                DNAME
-------------------- --------------------
JAMES                SALES
FORD                 RESEARCH
MILLER               ACCOUNTING

已选择14行。