oracle外部表的测试学习

 

一、外部表的特性
位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。对外部表的访问可以通过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"的日志文件,其中保存了出错的数据,用记事本打开看看那里出错,是否存在于外部表定义相冲突