datapumpdir oracle_外部表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行。