| 10G中将外部表用作数据传输 | DBA之路：OoNiceDream |

10G中将外部表用作数据传输

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/09/10g_external_table.html

外部表作为一种有效的数据传输机制，在Oracle 10G中有两种使用方式：
1、ORACLE_LOADER Access Driver
ORACLE_LOADER Access Driver是9i中延续下来的，与sqlloader有些类似，大致过程如下：
源数据库：

SQL> select * from perfstat.dept;
 
    DEPTNO DNAME                LOC
---------- -------------------- -------------------------
         1 a                    zj
         2 b                    zj
         3 c                    sh
 
SQL> spool external_dept.txt
SQL> select deptno||','||dname||','||loc||','
  2  from perfstat.dept;
 
DEPTNO||','||DNAME||','||LOC||','
-----------------------------------
1,a,zj,
2,b,zj,
3,c,sh,
 
SQL> spool off

目标数据库：
将external_dept.txt传输到目标数据库。

SQL> create or replace directory admin as '/home/oraadm';
 
Directory created.
 
SQL> grant read,write on directory admin to rockey;
 
Grant succeeded.
 
SQL> conn rockey/rockey
Connected.
SQL> CREATE TABLE dept (
  2     deptno     NUMBER(6),
  3     dname      VARCHAR2(20),
  4     loc        VARCHAR2(25) 
  5  )
  6  ORGANIZATION EXTERNAL
  7  (TYPE oracle_loader
  8   DEFAULT DIRECTORY admin
  9   ACCESS PARAMETERS
 10   (
 11    RECORDS DELIMITED BY newline
 12    BADFILE 'ulcase1.bad'
 13    DISCARDFILE 'ulcase1.dis'
 14    LOGFILE 'ulcase1.log'
 15    FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
 16    (
 17     deptno     INTEGER EXTERNAL(6),
 18     dname      CHAR(20),
 19     loc        CHAR(25)
 20    )
 21   )
 22   LOCATION ('external_dept.txt')
 23  )
 24  REJECT LIMIT UNLIMITED;
 
Table created.
 
SQL> select * from dept;
 
    DEPTNO DNAME                LOC
---------- -------------------- -------------------------
         1 a                    zj
         2 b                    zj
         3 c                    sh

2、ORACLE_DATAPUMP access driver
使用ORACLE_LOADER Access Driver方式，在spool时，如果表较大，导出可能会比较久。而在10G中还可以使用ORACLE_DATAPUMP access driver方式，将数据导出成类似于DUMP文件，可以在不同的操作系统中移植，速度比较快。

源数据库：

SQL> create or replace directory DUMP_DIR as '/home/ora10g';
 
Directory created.
 
SQL> create table dept_ext1
  2  organization external
  3  (
  4  type oracle_datapump
  5  default directory dump_dir
  6  location ('dept_dump.dmp')
  7  )
  8  as
  9  select * from perfstat.dept
 10  /
 
Table created.
 
SQL> select * from perfstat.dept;
 
    DEPTNO DNAME                LOC
---------- -------------------- -------------------------
         1 a                    zj
         2 b                    zj
         3 c                    sh
 
SQL> select * from dept_ext1;
 
    DEPTNO DNAME                LOC
---------- -------------------- -------------------------
         1 a                    zj
         2 b                    zj
         3 c                    sh

目标数据库：
将dept_dump.dmp传输到目标数据库。

SQL> create or replace directory admin as '/home/oraadm';
 
Directory created.
 
SQL> grant read,write on directory admin to perfstat;
 
Grant succeeded.
 
SQL> conn perfstat/perfstat
Connected.
SQL> create table dept_dump
  2  (
  3     deptno     NUMBER(6),
  4     dname      VARCHAR2(20),
  5     loc        VARCHAR2(25) 
  6  )
  7  organization external
  8  (
  9  type oracle_datapump
 10  default directory admin
 11  ACCESS PARAMETERS 
 12  (LOGFILE 'dept_dump.log')
 13  location ('dept_dump.dmp')
 14  );
 
Table created.
 
SQL> select * from dept_dump;
 
    DEPTNO DNAME                LOC
---------- -------------------- -------------------------
         1 a                    zj
         2 b                    zj
         3 c                    sh