oracle 创建外部表实例演示

青旅半醒 2022-02-17 01:17 412阅读 0赞

###  ###

*  一、外部表定义
 *  二、创建外部表
 *   *  2.1 创建外部表相关路径以及数据文件
     *  2.2创建数据库目录对象
     *  2.3 授权
     *  2.4 创建外部表
     *  2.5 查看外部表数据

# 一、外部表定义 #

外部表是这样的表，数据库只存放外部表的定义，外部表的数据存放在数据库之外，创建外部表的  
sql语句分成两部分，一分部分描述列的定义，另外一部分描述外部数据源如何映射成数据库的数据列。  
外部表的数据以文件的信息存放在操作中，简单地说，外部表数据存放在操作系统的文件中。  
想要使用外部表，用户必须有数据源所在目录读的权限（这里的目录是数据库对象）  
创建外部表时，要指定访问驱动（access driver），访问驱动由于把外部的数据读入oracle中。oracle提供两种驱动  
ORACLE\_LOADER,ORACLE\_DATAPUMP。默认的驱动是ORACLE\_LOADER  
同时需要注意的：外部表是只读的，因此不能在外部表执行任何的DML语句

# 二、创建外部表 #

## 2.1 创建外部表相关路径以及数据文件 ##

2.1.1、创建存放数据路径以及数据  
创建下面目录d:\\software\\oracle11\\ext\\data 并创建example.dat文件

1001,张三,音乐,0
    1002,李四,历史,87
    1003,王五,计算机,90
    1004,大仙,天文,60

创建路 d：\\software\\oracle11\\ext\\bad  
d:\\software\\oracle11\\ext\\log  
2.1.2 创建 存放日志文件目录 以及 坏文件的目录

d：\software\oracle11\ext\bad
    d:\software\oracle11\ext\log

## 2.2创建数据库目录对象 ##

在sys用户下

reate or replace directory ext_data_dir
    as 'd:\software\oracle11\ext\data';
    create or replace directory ext_log_dir
     as 'd:\software\oracle11\ext\log';
     create or replace directory ext_bad_dir
     as 'd:\software\oracle11\ext\bad';

## 2.3 授权 ##

oracle 要求：  
1）对存放数据的目录要有读的权限  
2）对存放日志文件的目录要有写的权限  
3）对存放坏文件的目录要有写的权限  
在sys用户下将外部表相关的目录对象授权给需要创建外部表的用户

grant read on directory ext_data_dir to oe;
     grant read,write on directory ext_log_dir to oe; -- 授予用户oe 有读写的权限
     grant read,write on directory ext_bad_dir to oe;

## 2.4 创建外部表 ##

在oe用户下创建外部表

drop table  ext_student;
    create table ext_student(
    id varchar2(4),
    name varchar2(20),
    major varchar2(15),
    score number
    )
    organization external  
    (
    type ORACLE_LOADER   
    default directory ext_data_dir  
    access parameters(
    records delimited by newline   
    badfile ext_bad_dir :'example%a_%p.bad'   
    logfile ext_log_dir :'example%a_%p.log'  
    fields terminated by ','    
    missing field values are null
    (id,name,major,score)
    )
    location ('example.dat')   
    )
    parallel
    reject limit unlimited;

关于创建外部表语句说明

organization external  --表示创建的是外部表
    (
    type ORACLE_LOADER   --指定访问的驱动是oracle_loader
    default directory ext_data_dir --指定数据的默认存放路径
     
    records delimited by newline  --一行为一条记录
    badfile ext_bad_dir:'example%a_%p.bad'  --指定坏文件的名字及路径
    logfile ext_log_dir:'example%a_%p.log'   -- 指定日志文件的名字及路径
    fileds terminated by ','    ---指定字段间 以',' 进行分隔
     
    location ('example.dat')  --指定存放数据文件的名字
     
    reject limit unlimited;  --表示允许无限制的行发生错误

## 2.5 查看外部表数据 ##

select  t.* from ext_student  t;

![在这里插入图片描述][20190418160142209.png]

从查询结果过看到，结果跟我们在本地数据文件example.dat写入的文件一样，  
外部表的引用，当我们已经得到导出的数据文件需要导入到数据库时，可以通过  
外部表的方式导入数据库。

[20190418160142209.png]: /images/20220217/93e626111df84b979ba2b676509dece6.png