2

【测试方案】oracle外部表的简单理解及应用 -利来国际app

jose chen 2021-11-05
316

前言

emmm,外部表嘛,顾名思义,就是在数据库外面的表,也就是存在操作系统的文件,格式有txt,csv等等,我们能用数据库语句去读取它,但是无法像正常的表一样给它加索引,或者执行删除及更新操作。
下面我们通过几个简单的实验,来感受下外部表是如何工作的。
ps:创建语句挺难记的,如果数据较少,我觉得还不如打开plsql,直接复制粘贴进去。

实验

part one 在数据库创建访问txt格式文件的外部表

1、创建路径
操作系统的文件目录,需要在数据库里体现并定义出来,比如我们定义d:\external_dir这个目录为external_dir,创建脚本

create directory external_dir as 'd:\external_dir';

注:我们可以通过dba_directories和all_directories两个视图查看数据库都定义了哪些路径

2、外部表利用哪个用户访问,我们需要将外部表所在的路径的访问权授予该用户,比方说scott,创建脚本

grant read,write on directory external_dir to scott;

3、我们在定义的路径里新建一个aa.txt文件,并写入如下数据

4、数据库层面登录到scott用户下,并创建外部表

create table dept_2     #创建的外部表表名
(
deptno number(2),
dname varchar2(14),      #定义字段类型
loc varchar2(13)
)
organization external
(type oracle_loader          #类似引擎,如果目标文件是文本用oracle_loaderer,如果是二进制文件,则用oracle_datapump
default directory external_dir   #选择之前数据库定义的路径
access parameters
( records delimited by newline
 fields terminated by ","         #表示数据文件里的数据用逗号隔开
)
location('aa.txt')                #文件名称
);
**ps:键入脚本的时候,标点符号要注意,使用英文的。**

5、创建成功,我们就可以使用select语句,查看文件数据了,但是无法执行删除或者更新语句,如果要把它变为本地表,可以使用子查询

create table tablename as select * from dept_2;

ps: 我们可以通过dba_external_tables&user_external_tables 视图查看数据库的外部表信息

part two 在数据库创建访问csv格式文件的外部表

前面的步骤和part one的一样,所以这里就不再赘述,唯一不同的是创建外部表脚本,脚本如下

create table dept_2
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(type oracle_loader                        
default directory external_dir
access parameters
( records delimited by newline
 fields terminated by ","
)
location('aaa.csv')                 #指定要加载的文件名称
)
reject limit unlimited;             #这句得加,不然会报ora-29913,ora-30653错误

part three 利用外部表功能将数据库表转化为二进制文件

外部表也能当数据迁移工具使用,将数据库的表转化为操作系统里的二进制文件,然后在别的数据库里重新加载为外部表。
1、同样需要在数据库里定义一个路径

create directory pumb_dir as 'd:\pumb_dir';

2、授权给scott用户

grant read,write on directory pumb_dir to scott;

3、这里将dept表转化为二进制文件

create table dept_4
organization external 
(type oracle_datapump        #二进制文件使用oracle_datapump
default directory pumb_dir
location('dept.dmp')         #文件名称
)
as
select * from dept;          将表dept的内容转化成二进制文件

4、执行成功,我们就得到了dept.dmp文件,如何在另一个数据库里加载该文件,我们在part four里讲

part four 在数据库创建访问二进制格式文件的外部表

延续part three的实验,我们将实验得到的dept.dmp文件,在另一个数据库加载并打开
1、创建路径,并将上述的dept.dmp文件放入该目录

create directory pumb_dir as 'd:\pumb_dir';

2、授权

grant read,write on directory pumb_dir to scott;

3、在数据库里创建外部表

create table dept_6           #表名
(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
)
organization external
(type oracle_datapump         #二进制文件使用oracle_datapump
default directory pumb_dir
location('dept.dmp')           #文件名称
);

4、此时我们就可以正常访问该外部表的数据了

后话

外部表的使用原理还是相对简单的,但是执行效率有待进一步确认,使用场景也有待考察,数据量较少的话,直接用plsql复制粘贴就可以了。

最后修改时间:2021-12-22 14:37:42
「喜欢文章,快来给作者赞赏墨值吧」
【利来手机国际的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:[email protected]进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论