oracle 创建外部表实例演示

一、外部表定义

外部表是这样的表，数据库只存放外部表的定义，外部表的数据存放在数据库之外，创建外部表的
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;

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