ORACLE外部表总结
Notice: 外部表是ORACLE 9i后引入的。
外部表特征
(1) 位于文件系统之中(一定要在数据库服务器中,而不是其它网络路径),按一定格式分割,
例如@#$等,文本文件或者其他类型的文件可以作为外部表。
(2) 对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。
(3) 外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。
(4) ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。
(5) 可以查询操作和连接。也可以并行操作。
(6) 数据在数据库的外部组织,是操作系统文件。
(7) 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
外部表范例:
1:创建目录对象并授权
从9i开始,ORACLE数据库若需要存取文件系统,就必须使用目录对象,以相对路径方式存取文件,强化数据库的安全性。建立目录对象、授予权限。
SQL CREATE OR REPLACE DIRECTORY DUMP_DIR AS /oradata/exterltab;
给用户授予指定目录的操作权限
SQL GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;
2:创建外部表
我创建了一个平面文件作为测试用例,如下所示,总共5条记录,其中一条错误记录
[oracle@DB-Server exterltab]$ more student.data
10001@#$kerry@#$male@#$28@#$1
10002@#$jimmy@#$male@#$22@#$1
10003@#$ken@#$male@#$21@#$1
10004@#$merry@#$femal@#$20@#$1
this is a bad file
外部表语法也是蛮复杂的,参数选项非常多,这里不做过多解释。有兴趣自然可以翻阅官方文档。
出现下面错误,是因为student.data 文件中有不符合规范的记录,可以删除“this is a bad file”这一条记录,但是这是为了测试下面情况,所以可以通过执行 alter table exter_test reject limit unlimited;跳过一些限制。
此时去查看/oradata/exterltab,你会发现自动生成了EXTER_TEST_8907.bad 和 EXTER_TEST_8907.log两个文件,其中log记录访问外部表的记录信息,bad文件记录错误记录的信息。大体如下所示
[oracle@DB-Server exterltab]$ ls
EXTER_TEST_8907.bad EXTER_TEST_8907.log student.data
[oracle@DB-Server exterltab]$
3:查看外部表的目录
xxx_external_locations 可以知道当前所有的目录对象以及相关的外部表,还会查询出这些外部表所对应的操作系统文件的名字。
select * from all_external_locations;
select * from user_external_locations;
select * from dba_external_locations;
--------- -------------------------- --------------- ---------------------------
SH SALES_TRANSACTIONS_EXT sale1v3.dat SYS DATA_FILE_DIR
ETL EXTER_TEST student.data SYS DUMP_DIR
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)
PROPERTY VARCHAR2(10)
删除外部表SQL语法跟普通表一样,但是不同之 处在于有可能还要删除与之对应的目录对象。当外部表不用时,需要及时删除外部表或者与之对应的目录对象。不过在删除这些内容时会有一些限制。这些限制主要 是管理上的限制,而不是技术上的限制。也就是说,Oracle数据库系统没有对其进行强制的限制。但是如果数据库管理员不遵守这些限制的话,可能会出现一 些问题。如要先删除外部表,然后再删除目录对象。有时候一个目录对象中可能会包含多个外部表。此时必须要确认所有的外部表都不用了,都已经删除干净了,然 后才能够删除目录对象。在创建外部表时,操作系统会判断一下,与之对应的目录对象是否已经创建。但是在删除对象时,系统不会去判断跟这个目录对象关联的外 部表是否已经全部删除。如果目录对象删除了,但是还有外部表存在。此时查询这个外部表的时候,系统就会提示“对象不存在”的错误信息。所以这个删除目录对 象时,数据库系统缺乏一种检查,此 时只有数据库管理员在删除目录对象时,先手工确认一下这个目录对象是否存在其他的外部表。
外部表限制:
1. 只能对表进行SELECT,不能进行DELETE、UPDATE、INSERT这些DML操作。
2. 因为外部表需要在ORACLE数据库“服务端”创建目录,OS文件必须放在这些目录中。即这些文件只能放在数据库服务端。如果数据文件不位于服务器,则无法使用外部表
3. 外部表上不能创建索引。但可以建立视图
4. 外部表不支持LOB对象。如果要使用LOB类型,则不能使用外部表。
eg:删除外部表的记录
外部表优势:
如果要谈外部表的优势,一般会和SQLLDR来对比,外部表很多语法跟SQLLDR控制文件确实有很多类似的地方,下面谈谈自己的理解和"Oracel 9i 10g编程艺术"里面的一些对比
1. SQLLDR需要将数据装载入库后才能查询相关记录,如果只是为了查询一些记录,外部表确实比SQLLDR要有优势一些,很方便又不占用数据库存储空间。 尤其是很大的数据,以前做移动综合分析项目处理SGSN话单(几百G的数据,如果全部装载入库,非常浪费空间和时间)时就有这样的体会。外部表虚拟的导入 过程极快
2:当平面文件改变时,外部表内的数据会跟着改变。这样避免了插入、更新、删除等操作,对于超大记录的外部表相当有优势,我只需要使用Shell命令就能搞定数据库需要很高代价才能完成的事情。
3:外部表可以使用复杂的WHERE 条件有选择地加载数据。尽管SQLLDR 有一个WHEN 子句用来选择要加载的行,但是你只能使用AND 表达式和执行相等性比较的表达式,在WHEN 子句中不能使用区间(大于、小于),没有OR 表达式,也没有IS NULL 等。
4:能够合并(MERGE)数据。可以取一个填满数据的操作系统文件,并由它更新现有的数据库记录。
5:能执行高效的代码查找。可以将一个外部表联结到另一个数据库表作为加载过程的一部分。
6:使用INSERT 更容易地执行多表插入。从Oracle9i 开始,通过使用复杂的WHEN 条件,可以用一个INSERT 语句插入一个或多个表。尽管SQLLDR 也可以加载到多个表中,但是相应的语法相当复杂。
Oracle数据库面试题总结 1、SQL语句分类 DQL(数据查询语言)select DML(数据操作语言)insert、delete、update DDL(数据定义语言)create、drop、alter DCL(数据控制语言)grant:把权限授予用户、revoke:把权限从用户收回 TPL(TCL,事务控制语言):commit、rollback
Oracle总结【SQL细节、多表查询、分组查询、分页】下 在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
Oracle总结【SQL细节、多表查询、分组查询、分页】上 在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用… 实例与数据库概念
Oracle总结【视图、索引、事务、用户权限、批量操作】三 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
Oracle总结【视图、索引、事务、用户权限、批量操作】二 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
Oracle总结【视图、索引、事务、用户权限、批量操作】 一 在Oracle总结的第一篇中,我们已经总结了一些常用的SQL相关的知识点了…那么本篇主要总结关于Oralce视图、序列、事务的一些内容… 在数据库中,我们可以把各种的SQL语句分为四大类… (1)DML(数据操纵语言):select,insert,update,delete (2)DDL(数据定义语言):create table,alter table,drop table,truncate table (3)DCL(数据控制语言):grant select any table to scott/revoke select any table from scott (4)TCL(事务
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
相关文章
- 编写Oracle存储过程:操作技巧总结(oracle编写存储过程)
- 技术总结Oracle年末技术总结:步步惊心!(oracle年末)
- 均衡Oracle负载均衡:提升服务器性能的利器(oracle负载)
- Oracle导出表数据的全部指令总结(oracle导出表数据命令)
- 探索Oracle查询外部表的方法(oracle查询外部表)
- 深圳:Oracle服务器维护专家只要一步之遥(深圳oracle代理)
- Oracle数据库的外部访问实践(oracle外部访问)
- 构建Oracle数据库外部表:一步一步走(创建oracle外部表)
- Oracle分类汇总——全面总结Oracle数据库的各个方面,助您提高技能水平!(oracle分类汇总)
- Oracle HowTo:轻松构建外部表(oracle外建)
- Oracle 内存表研究之总结(oracle 内存表总结)
- 开启CX Oracle源码包之旅(cx oracle源码包)
- 32位Oracle系统优势与弊端(32 oracle)
- 优化ef批量Oracle数据库优化实战总结(ef 批量oracle)
- Oracle创建临时外部表一个指南(oracle 临时外部表)
- 查询Oracle中探索递归查询的秘密(oracle中的递归)
- 实战经验技巧Oracle使用攻略(oracle使用总结)
- Oracle 会话状态系统稳定性的基础(oracle 会话状态)
- 郑州带来Oracle最佳方案的代理商(oracle代理商 郑州)
- 妙用Oracle中的常见符号(oracle中的常用符号)
- Oracle中有效的列名规则总结(oracle中有效的列名)
- 处理Oracle处理数值类型的知识总结(oracle 中数值类型)
- Oracle中创建索引的步骤及方法(oracle中如何创建索引)
- 揭秘Oracle中的Real型数据(oracle中real)
- Oracle 计算两个日期之间的差距(oracle两个日期的差)
- Oracle上机实验收获丰硕(oracle上机实验总结)