暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片

【测试方案】Oracle外部表的简单理解及应用

原创 Jose Chen 2021-11-05
1619

前言

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文件,并写入如下数据
16360752441.png

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
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论