oracle 中使用外部表做数据迁移

TOM 的9I &10G书上有比较完事的例子，大家可以看看.

不用exp/imp,expdp/impdp来不同平台间迁移数据表

在第一台数据库服务器上操作（windows）：

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 1月 16 11:18:59 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> conn sys/*** as sysdba
已连接。

SQL> create or replace directory tmp as 'C:/tmp'
  2  /

目录已创建。

SQL> create table user_tables_unload
  2  organization external
  3  ( type oracle_datapump
  4    default directory TMP
  5    location( 'usertables.dat' )
  6  )
  7  as
  8  select
  9  *
10  from user_tables
11  /

表已创建。

这时在C:/tmp目录下会生成两个文件：一个日志文件USER_TABLES_UNLOAD_712_2968.log、一个数据文件USERTABLES.DAT
用FTP工具把USERTABLES.DAT文件上传到另外一台数据库服务器(LINUX+ORACLE10g)的/tmp目录下

SQL> select dbms_metadata.get_ddl( 'TABLE', 'USER_TABLES_UNLOAD' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."USER_TABLES_UNLOAD"
   (    "TABLE_NAME" VARCHAR2(30),
        "T

注意：这里可能显示不全，用set命令，如下

SQL> set long 2000;
SQL> /

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."USER_TABLES_UNLOAD"
   (    "TABLE_NAME" VARCHAR2(30),
        "TABLESPACE_NAME" VARCHAR2(30),
        "CLUSTER_NAME" VARCHAR2(30),
        "IOT_NAME" VARCHAR2(30),
        "STATUS" VARCHAR2(8),
        "PCT_FREE" NUMBER,
        "PCT_USED" NUMBER,
        "INI_TRANS" NUMBER,
        "MAX_TRANS" NUMBER,

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------
        "INITIAL_EXTENT" NUMBER,
        "NEXT_EXTENT" NUMBER,
        "MIN_EXTENTS" NUMBER,
        "MAX_EXTENTS" NUMBER,
        "PCT_INCREASE" NUMBER,
        "FREELISTS" NUMBER,
        "FREELIST_GROUPS" NUMBER,
        "LOGGING" VARCHAR2(3),
        "BACKED_UP" VARCHAR2(1),
        "NUM_ROWS" NUMBER,
        "BLOCKS" NUMBER,

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------
        "EMPTY_BLOCKS" NUMBER,
        "AVG_SPACE" NUMBER,
        "CHAIN_CNT" NUMBER,
        "AVG_ROW_LEN" NUMBER,
        "AVG_SPACE_FREELIST_BLOCKS" NUMBER,
        "NUM_FREELIST_BLOCKS" NUMBER,
        "DEGREE" VARCHAR2(20),
        "INSTANCES" VARCHAR2(20),
        "CACHE" VARCHAR2(10),
        "TABLE_LOCK" VARCHAR2(8),
        "SAMPLE_SIZE" NUMBER,

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------
        "LAST_ANALYZED" DATE,
        "PARTITIONED" VARCHAR2(3),
        "IOT_TYPE" VARCHAR2(12),
        "TEMPORARY" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NESTED" VARCHAR2(3),
        "BUFFER_POOL" VARCHAR2(7),
        "ROW_MOVEMENT" VARCHAR2(8),
        "GLOBAL_STATS" VARCHAR2(3),
        "USER_STATS" VARCHAR2(3),
        "DURATION" VARCHAR2(15),

DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------
        "SKIP_CORRUPT" VARCHAR2(8),
        "MONITORING" VARCHAR2(3),
        "CLUSTER_OWNER" VARCHAR2(30),
        "DEPENDENCIES" VARCHAR2(8),
        "COMPRESSION" VARCHAR2(8),
        "DROPPED" VARCHAR2(3)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "TMP"


DBMS_METADATA.GET_DDL('TABLE','USER_TABLES_UNLOAD')
--------------------------------------------------------------------------------
      LOCATION
       ( 'usertables.dat'
       )
    )


第二台服务器上进行如下操作
$ pwd
/tmp
$ ls *.DAT               (注意大小写噢)
USERTABLES.DAT

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 16 11:29:08 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> create or replace directory tmp as '/tmp';

Directory created.

SQL> CREATE TABLE "SYS"."USER_TABLES_UNLOAD"
  2     (    "TABLE_NAME" VARCHAR2(30),
  3          "TABLESPACE_NAME" VARCHAR2(30),
  4          "CLUSTER_NAME" VARCHAR2(30),
  5          "IOT_NAME" VARCHAR2(30),
  6          "STATUS" VARCHAR2(8),
  7          "PCT_FREE" NUMBER,
>>>>>>>>>>>>>>>>>部分省略<<<<<<<<<<<<<<<<<<<<<<<<<<<
47          "DEPENDENCIES" VARCHAR2(8),
48          "COMPRESSION" VARCHAR2(8),
49          "DROPPED" VARCHAR2(3)
50     )
51  ORGANIZATION EXTERNAL
52      ( TYPE ORACLE_DATAPUMP
53        DEFAULT DIRECTORY "TMP"
54        LOCATION
55      ( 'USERTABLES.DAT')                           (注意大小写噢)
56      )
57  /

Table created.

SQL> SELECT COUNT(*) FROM USER_TABLES_UNLOAD;

  COUNT(*)
----------
       701
 oracle 10g中外部表type有两种类型:ORACLE_LOADER,ORACLE_DATAPUMP
外部表只适合查询用.