ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE 外部表

ORACLE 外部表

原创 Linux操作系统 作者:tomshenhao 时间:2011-09-11 10:35:37 0 删除 编辑
 

外部链接:

查看: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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24751738/viewspace-707177/,如需转载,请注明出处,否则将追究法律责任。

上一篇: ORACLE flashback
下一篇: ORACLE 导入导出
请登录后发表评论 登录
全部评论

注册时间:2011-07-14

  • 博文量
    260
  • 访问量
    393337