ORACLE External Table

外部链接：

查看：select * from dba_db_links

查看当前使用：select * from v$dblink

创建：create database link 名字 connect to 用户名 identified by 密码 using '数据库SID';

删除：drop database link 名字;

类型：

pubilic-->shared 公用

private --> non-shared 私有（默认）

外部表：

作用：方便数据迁移，不用导入导出

缺点：不能DML

做数据库迁移是要注意外部表

创建外部表：

1. oracle_datatpump形式，相关文件为二进制格式存放

a. 把源数据库的表产生出数据文件

创建路径：create directory datamove as '/tmp/movedata';

create table 名字 organization external (type oracle_datapump default directory  目录名 location('文件名'))  as 内容

例如：

create table datapump
  organization external (
  type oracle_datapump
  default directory datamove
  location('dump.dmp')
   )
   as
   select employee_id,first_name,salary,hire_date from  hr.employees

b. 在目标数据库以外部表方式访问：

创建路径：create directory datamove as '/tmp/movedata';

创建外部表：

CREATE TABLE emp_xt2
    (
    "EMPLOYEE_ID" NUMBER(6),
  "FIRST_NAME" VARCHAR2(20),
  "SALARY" NUMBER(8,2),
  "HIRE_DATE" DATE
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY datamove

     LOCATION ('dump.dmp')
   );

2. sql loader形式，相关文件为二进制格式存放

创建脚本生成：

a. 准备SQL Loader需要的控制文件，数据文件

例如：

控制文件：

cardata.ctl

LOAD DATA
INFILE '/tmp/movedata/cardata.txt'
BADFILE '/tmp/movedata/cardata.bad'
DISCARDFILE '/tmp/movedata/cardata.dsc'
TRUNCATE
INTO TABLE "HR"."DATAMV"
WHEN EMPLOYEE_ID<>'188'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(EMPLOYEE_ID,
FIRST_NAME,
SALARY,
HIRE_DATE DATE "DD-MON-YY")

数据文件：

cardata.txt

179,Charles,6835.5,04-JAN-00
180,Winston,3528,24-JAN-98
181,Jean,3417.75,23-FEB-98
182,Martha,2756.25,21-JUN-99
183,Girard,3087,03-FEB-00
184,Nandita,4630.5,27-JAN-96
185,Alexis,4520.25,20-FEB-97
186,Julia,3748.5,24-JUN-98
187,Anthony,3307.5,07-FEB-99
188,Kelly,4189.5,14-JUN-97
189,Jennifer,3969,13-AUG-97
189,ERROR,3969,13-AUGs-9722
189,ERROR,3969S,13-AUG-9722

b. 数据库中根据控制文件要求创建数据表"HR"."DATAMV"

c. 将两个文件上传到服务器某个文件夹

d. 在服务器该文件夹下运行：sqlldr hr/hr control=cardata.ctl log=cardata.log external_table=GENERATE_ONLY

e. 在cardata.log文件中找出脚本并修改成最终脚本

f. 可以在不同数据库运行最终脚本来创建外部表

最终脚本：

CREATE TABLE hr.sqlldr
(
  "EMPLOYEE_ID" NUMBER(6),
  "FIRST_NAME" VARCHAR2(20),
  "SALARY" NUMBER(8,2),
  "HIRE_DATE" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY datamove
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    LOAD WHEN ("EMPLOYEE_ID" != "188")
    BADFILE 'cardata.bad'
    DISCARDFILE 'cardata.dsc'
    LOGFILE 'cardata.log'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "EMPLOYEE_ID" CHAR(255)
        TERMINATED BY ",",
      "FIRST_NAME" CHAR(255)
        TERMINATED BY ",",
      "SALARY" CHAR(255)
        TERMINATED BY ",",
      "HIRE_DATE" CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "DD-MON-YY"
    )
  )
  location
  (
    'cardata.txt'
  )
)REJECT LIMIT UNLIMITED

查看外部表：

select * from dba_external_tables

查看外部表具体内容：

select * from dump_datamv;

删除外部表：(需要手工将外部表文件删除)

drop table dump_datamv;