ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle外部表的测试学习 (转)

oracle外部表的测试学习 (转)

原创 Linux操作系统 作者:流浪的野狼 时间:2013-09-30 16:50:32 0 删除 编辑

一、外部表的特性
位于文件系统之中,按一定格式分割,如文本文件或者其他类型的表可以作为外部表。对外部表的访问可以通过SQL语句来完成,而不需要先将外部表中的数据装载进数据库中。外部数据表都是只读的,因此在外部表不能够执行DML操作,也不能创建索引。ANALYZE语句不支持采集外部表的统计数据,应该使用DMBS_STATS包来采集外部表的统计数据。

二、创建外部表的注意事项
1.需要先建立目录对象
2.对于操作系统文件的要求
文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件
3.在建立临时表时的相关限制
对表中字段的名称存在特殊字符的情况下,必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。对于列名字中特殊符号未采用双引号括起来时,会导致无法正常查询数据。建议不用使用特殊的列标题字符。
在创建外部表的时候,并没有在数据库中创建表,也不会为外部表分配任何的存储空间。创建外部表只是在数据字典中创建了外部表的元数据,以便对应访问外部表中的数据,而不在数据库中存储外部表的数据。简单地说,数据库存储的只是与外部文件的一种对应关系,如字段与字段的对应关系。而没有存储实际的数据。由于存储实际数据,故无法为外部表创建索引,同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。
4.删除外部表或者目录对象
一般情况下,先删除外部表,然后再删除目录对象,如果目录对象中有多个表,应删除所有表之后再删除目录对象。如果在未删除外部表的情况下,强制删除了目录,在查询到被删除的外部表时,将收到"对象不存在"的错误信息。查询dba_external_locations来获得当前所有的目录对象以及相关的外部表,同时会给出这些外部表所对应的操作系统文件的名字。
5.对于操作系统平台的限制
不同的操作系统对于外部表有不同的解释和显示方式如在Linux操作系统中创建的文件是分号分隔且每行一条记录,但该文件在Windows操作系统上打开则并非如此。建议避免不同操作系统以及不同字符集所带来的影响

三、外部表的创建
这里使用一个简单常用的alterlog创建外部表的实例来作为测试
首先创建一个目录
create or replace directory dir_alert as '/opt/oracle/admin/test/bdump';
grant read,write on directory dir_alert to yuzh;

创建外部表
create table alert_log
( text varchar2(400) )
organization external
(
 type oracle_loader
default directory dir_alert
access parameters (
                  records delimited by newline
                  nobadfile
                  nodiscardfile
                  nologfile
                  )
location('alert_test.log')
)
reject limit unlimited;
因为没用分割符号,所以文件中的每一行对应外部表的每一行,所以这里创建外部表只有一个字段。数据的默认排序和文件中的记录行一致,先进的序号在前,后进的序号在后。

再来个使用分隔符的表
create table test_e
(
     cl1 number(10),
     cl2 varchar2(20)
)
 organization external
(
   type oracle_loader
 default directory dir_alert
 access parameters
 (
     records delimited by newline
     fields terminated by ','
  )
location
  ('text1.txt','text2.txt')
) reject limit unlimited;

自己去建立个两个txt文件写条记录进去,查询一下
SQL> select * from test_e;
 
        CL1 CL2
----------- --------------------
    1111111 aaaaaa
     222222 bbbbbb
    
这里注意的以上例子都是使用的type oracle_loader

其实还有一种type oracle_datapump
这个算是建立一个外部文件和外部表了,一般来说导数据时候用用不错的,来个测试用例
create table test_d
organization external
(
  type oracle_datapump
  default directory dir_alert
  location('text3.txt','text4.txt')
)
parallel
as select * from all_objects

执行完毕后去系统目录下看到3和4的文件,然后把这两个文件拷贝到别的机器上,再通过建立外部表的模式把相应诗句倒动到其他表中即可。
查询一下:
SQL> select * from test_d where rownum<10;
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
PUBLIC                         GV$RESUMABLE                                                        2081                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$TIMEZONE_NAMES                                                   2083                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$TIMEZONE_FILE                                                    2085                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$ENQUEUE_STAT                                                     2087                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$ENQUEUE_STATISTICS                                               2089                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$LOCK_TYPE                                                        2091                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$RMAN_CONFIGURATION                                               2093                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$VPD_POLICY                                                       2095                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         V$VPD_POLICY                                                        2097                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
 
9 rows selected

数据太多,显示几条意思意思就行了,呵呵

四、外部表的使用
--更改拒绝限制
ALTER TABLE alert_log LIMIT 100;
--更改默认目录说明
ALTER TABLE alert_log DIRECTORY DEFAULT DIRECTORY bdump;
--修改访问参数,如分隔符由","变为"|"
ALTER TABLE alert_log PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改文件位置:
ALTER TABLE alert_log LOCATION('aaa.txt');
--删除表
drop table alert_log ;
--删除目录
drop DIRECTORY bdump;

一个简单的查询文件最后500行中是否有ora的错误(注:这个数据量少的时候速度不慢,但是大数据量时就不太好说了)
select *
  from (select rownum as rno, text, (select count(*) from alert_log) allnum
          from alert_log)
 where rno > allnum - 500
   and lower(text) like '%ora-%'

还有这种使用偏移量的函数来实现取前后多少行记录的语句(不过我觉得不是太靠谱,对默认值的唯一性要求高,效率我也不知道快不快)
取前10条(我取默认值0)
select text
  from (select text, lag(rno, 10, 0) over(order by rno) dd
          from (select rownum as rno, text from alert_log))
 where dd = '0'
   and lower(text) like '%ora-%';
取后十条
select text
  from (select text, lead(rno, 10, 0) over(order by rno) dd
          from (select rownum as rno, text from alert_log))
 where dd = '0'
   and lower(text) like '%ora-%';

这里简单介绍下偏移量函数
lag函数就是把rno的值往后移动n位,这样移动后函数值的结果就是n前的都补了默认值,n后的函数结果就是往前数n位的rno值。
lead函数也是一样的,只是反过来罢了。
 
五、其他说明  

相关的系统视图
select * from DBA_EXTERNAL_TABLES;
select * from DBA_EXTERNAL_LOCATIONS;

外部表定义的几个重点
a.ORGANIZATIONEXTERNAL关键字,必须要有。以表明定义的表为外部表。
b.重要参数外部表的类型
ORACLE_LOADER:定义外部表的缺省方式,只能只读方式实现文本数据的装载。
ORACLE_DATAPUMP:支持对数据的装载与卸载,数据文件必须为二进制dump文件。可以从外部表提取数据装载到内部表,也可以从内部表卸载数据作为二进制文件填充到外部表。
c.DEFAULTDIRECTORY:缺省的目录指明了外部文件所在的路径
d.LOCATION:定义了外部表的位置
f.ACCESS PARAMETERS:描述如何对外部表进行访问
RECORDS关键字后定义如何识别数据行 
DELIMITEDBY'XXX'——换行符,常用newline定义换行,并指明字符集。对于特殊的字符则需要单独定义,如特殊符号,可以使用OX'十六位值',例如tab(/t)的十六位是9,则DELIMITEDBY0X'09';cr(/r)的十六位是d,那么就是DELIMITEDBY0X'0D'。 SKIP X ——跳过X行数据,有些文件中第一行是列名,需要跳过第一行,则使用SKIP 1。
FIELDS关键字后定义如何识别字段,常用的如下:
FIELDS:TERMINATEDBY'x'——字段分割符。
ENCLOSEDBY'x'——字段引用符,包含在此符号内的数据都当成一个字段。例如一行数据格式如:"abc","a""b,""c,"。使用参数TERMINATEDBY','ENCLOSEDBY'"'后,系统会读到两个字段,第一个字段的值是abc,第二个字段值是a"b,"c,。
LRTRIM ——删除首尾空白字符。
MISSING FIELDVALUESARENULL——某些字段空缺值都设为NULL。
对于字段长度和分割符不确定且准备用作外部表文件,可以使用UltraEdit、Editplus等来进行分析测试,如果文件较大,则需要考虑将文件分割成小文件并从中提取数据进行测试。
      
外部表对错误的处理
REJECT LIMIT UNLIMITED
在创建外部表时最后加入LIMIT子句,表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制
BADFILE和NOBADFILE子句
用于指定将捕获到的转换错误存放到哪个文件。如果指定了NOBADFILE则表示忽略转换期间的错误如果未指定该参数,则系统自动在源目录下生成与外部表同名的.BAD文件BADFILE记录本次操作的结果,下次将会被覆盖
LOGFILE和NOLOGFILE子句
同样在accessparameters中加入LOGFILE'LOG_FILE.log'子句,则所有Oracle的错误信息放入'LOG_FILE.log'中而NOLOGFILE子句则表示不记录错误信息到log中,如忽略该子句,系统自动在源目录下生成与外部表同名的.LOG文件
 
注意以下几个常见的问题
1.外部表经常遇到BUFFER不足的情况,因此尽可能的增大READSIZE
2.换行符不对产生的问题。在不同的操作系统中换行符的表示方法不一样,碰到错误日志提示如是换行符问题,可以使用UltraEdit打开,直接看十六进制
3.特定行报错时,查看带有"BAD"的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突

转自:http://blog.csdn.net/yuzhenhuan01/article/details/6688976

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28612416/viewspace-773659/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle资源管理器
请登录后发表评论 登录
全部评论

注册时间:2013-04-10

  • 博文量
    151
  • 访问量
    1507811