Oracle外部表详解

表表概述

外部表只能在Oracle
9i之后来以。简单地说,外部表,是乘无设有被数据库被之申。通过奔Oracle提供描述外部表的首家数据,我们得将一个操作系统文件当成一个独读的数目库表,就像这些数据存储于一个普通数据库表中一样来进行走访。外部表是对数据库表的延。

外部表的性状 

置身文件系统之中,按自然格式分割,如文本文件要其它品类的发明可以当外部表。
对外部表的走访可以由此SQL语句来形成,而不欲先拿表面表中的数据装载进数据库中。
外部数据表都是不过念之,因此当外部表不可知推行DML操作,也未能够创建索引。
ANALYZE语句不支持采集外部表的统计数据,应该下DMBS_STATS包来集外部表的统计数据。

始建外部表的注意事项 

1.索要事先成立目录对象

当确立目标的上,需要小心,Oracle数据库系统非见面失掉肯定这目录是否真正在。如果在输入这个目录对象的时节,不小心将路子写错了,那可能是外
部表仍然可健康建立,但是可力不从心查询到数码。由于起目录对象时,缺乏这种自己检讨的体制,为这个于用路径赋予给这目录对象时,需要专门之注意。另外需
要专注的凡途径的高低写。在Windows操作系统中,其路径是未区分轻重缓急写的。而以Linux操作系统,这个路子需要区分轻重缓急写。故在不同之操作系统
中,建立目录对象时需要小心是尺寸写的差距

2.对于操作系统文件的渴求

建立外部表时,必须指定操作系统文件所采取的隔符号。并且该分隔符发且仅发生一个。创建外部表时,不可知含有标题列。如果这题目信息与表面表的字段类型不平等(如字段内容是number数据类型,而标题信息则是字符型数据,则在询问时就是会见错)。如果数据类型恰巧一致的话,这个标题信息Oracle数据库也会作为普通记录来对待。

当Oracle数据库系统访问是操作系统文件的下,会于是文件所在的目自动创建一个日志文件。无论最终是否访问成功,这个日志文件还见面准时建立。查看这日志文件,可以了解数据库访问外部表的频率、是否中标访问等等。默认情况下,该日记在和外表表的同样directory下出。

3.当起临时表时的相关限制

本着发明中字段的名称在特殊字符的景况下,必须采用英文状态的生的对引号将拖欠表列名称连接起来。如采取”SalseID#”。
对列名字中特殊符号未以双招号括起来时,会招力不从心正常查询数据。
建议并非采用非常之列标题字符
在创立外部表底时节,并没有以数据库被开创建表,也不见面吧表表分配任何的贮存空间。
创办外部表只是在数量字典中创造了外部表底头版数据,以便对应访问外部表中的数据,而休以数据库被储存外部表的数量。
粗略地说,数据库存储的唯有是暨外部文件之同一种植对诺涉及,如字段与字段的附和关系。而尚未存储实际的数量。
由于存储实际数据,故无法也表表创建索引,同时于多少运用DML时为非支持对外部表的插入、更新、删除等操作。

4.删减外部表要目录对象

貌似情况下,先去外部表,然后再次去目录对象,如果目录对象吃生出多单说明,应除去所有表之后再度去目录对象。
假定在无删减外部表的情状下,强制删除了目录,在询问及让剔除的外表表时,将接”对象非有”的错误信息。
查询dba_external_locations来取得当前拥有的目录对象同有关的外表表,同时会被有这些外部表所对应之操作系统文件的名。 如果只是当数据库层面达到抹外部表,并无会见自动删除操作系统及的外表表文件。

 5.对于操作系统平台的限制

差之操作系统对于外部表出不同的说明和显示方式
如果在Linux操作系统被开创的公文是分号分隔且各行一长记下,但该公文于Windows操作系统及开拓则并非如此。
提议避免不同操作系统及不同字符集所带的熏陶

创立外部表 

动用CREATE TABLE语词的ORGANIZATION
EXTENERAL子句来创造外部表。外部表不分红任何盘区,因为只是于数额字典中创造首批数据。

1.外部表的创办语法

createtabletable_name
           (col1 datatype1,col2 datatype2,col3 datatype3)
            organization exteneral
           (…..)
翔语法可参见笔者的外两首文章

Oracle外部表ORACLE_DATAPUMP类型的始建语法详解:http://czmmiao.iteye.com/blog/1268453

Oracle外部表ORACLE_LOADER类型的始建语法详解:http://czmmiao.iteye.com/blog/1268157

2.是因为询问结果集,使用Oracle_datapump来填充数据来转外部表

a.创建系统目录以及Oracle数据目录名来建立针对承诺涉及,同时给予权限

$ mkdir -p /home/oracle/external_tb/data

create or replace directory data_dir as '/home/oracle/external_tb/data/';
grant read,write on directory data_dir to scott;

b.创建外部表

create table ex_tb1
            (ename,job,sal,dname)
            organization external
            (type oracle_datapump default directory data_dir location('ex_tb1'))
            parallel 1
            as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

c.验证外部表

select * from ex_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

对此以上述方法创造的标表得用其复制到其他途径作为外部表底原有数据来十分成新的外部表,用于转移数据。

d.将表面表文件复制一个初的公文称,用以模拟到其它服务器上

$ cp /home/oracle/external_tb/data/ex_tb1 /home/oracle/external_tb/data/in_tb1

e. 新建表,将上述外部表的多寡导入到新表中

create table in_tb1
            (ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14))
            organization external
            (type oracle_datapump default directory data_dir location('in_tb1'));

f.验证新标表底数据

select * from in_tb1;

ENAME                       JOB           SAL  DNAME
------------------------- -------------------- ---- -------------------------
CLARK                  MANAGER              2450 ACCOUNTING
KING                     PRESIDENT             5000 ACCOUNTING
MILLER                   CLERK                 1300 ACCOUNTING
JONES                    MANAGER               2975 RESEARCH
FORD                     ANALYST               3000 RESEARCH
ADAMS                    CLERK                 1100 RESEARCH
SMITH                    CLERK                  800 RESEARCH
SCOTT                    ANALYST               3000 RESEARCH
WARD                     SALESMAN              1250 SALES
TURNER                   SALESMAN              1500 SALES
ALLEN                    SALESMAN              1600 SALES
JAMES                    CLERK                  950 SALES
BLAKE                    MANAGER               2850 SALES
MARTIN                   SALESMAN              1250 SALES

14 rows selected.

g.创建健康的发明,将标表数据导入,这即是应用ORACLE_DATAPUMP类型的脑门外部表实现多少迁移

create table tb1 as select * from in_tb1;

3.运用外部文件数量,使用oracle_loader来填充数据来扭转外部表

 a.准备外部数据源文件

cat /home/oracle/external_tb/data/1.txt
"7369","SMITH","CLERK","7902","17-DEC-80","100","0","20"
"7499","ALLEN","SALESMAN","7698","20-FEB-81","250","0","30"
"7521","WARD","SALESMAN","7698","22-FEB-81","450","0","30"
"7566","JONES","MANAGER","7839","02-APR-81","1150","0","20"

$ cat /home/oracle/external_tb/data/2.txt
"7654","MARTIN","SALESMAN","7698","28-SEP-81","1250","0","30"
"7698","BLAKE","MANAGER","7839","01-MAY-81","1550","0","30"
"7934","MILLER","CLERK","7782","23-JAN-82","3500","0","10"

b.创建外部表

create table emp_new(
                    emp_id number(4),
                    ename varchar2(15),
                    job varchar2(12),
                    mgr_id number(4),
                    hiredate date,
                    salary number(8),
                    comm number(8),
                    dept_id number(2)
                    )
            organization external
                    (
                    type oracle_loader
                    default directory data_dir
                    access parameters(
                                    records delimited by newline
                                    badfile 'emp_new%a_%p.bad'
                                    logfile 'emp_new%a_%p.log'
                                    fields terminated by ','
                                    optionally enclosed by '"'
                                    lrtrim missing field values are null
                                    reject rows with all null fields
                                    )
                    location ('1.txt','2.txt')
)
parallel 
reject limit unlimited;

c.验证外部表

select * from emp_new;

EMP_ID ENAME      JOB              MGR_ID    HIREDATE            SALARY     COMM       DEPT_ID
------ ---------- --------------- ---------- ------------------- ---------- ---------- ----------
  7654 MARTIN     SALESMAN        7698       1981-09-28 00:00:00 1250       0           30
  7698 BLAKE      MANAGER         7839       1981-05-01 00:00:00 1550       0           30
  7934 MILLER     CLERK           7782       1982-01-23 00:00:00 3500       0           10
  7369 SMITH      CLERK           7902       1980-12-17 00:00:00 100        0           20
  7499 ALLEN      SALESMAN        7698       1981-02-20 00:00:00 250        0           30
  7521 WARD       SALESMAN        7698       1981-02-22 00:00:00 450        0           30
  7566 JONES      MANAGER         7839       1981-04-02 00:00:00 1150       0           20

7 rows selected.

 4.标表相关视图

a.查看表面表信息

select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;

 

b.获得平面文件的职

select * from user_external_locations order by table_name;

TABLE_NAME LOCATION   DIRECTORY DIRECTORY_NAME
---------- ---------- --------- --------------------
EMP_NEW    1.txt      SYS       DATA_DIR
EMP_NEW    2.txt      SYS       DATA_DIR
EX_TB1     ex_tb1     SYS       DATA_DIR
IN_TB1     in_tb1     SYS       DATA_DIR

 

表表定义的几只重大 

1.ORGANIZATION EXTERNAL重点字,必须使发。以标明定义的表为外部表。

2..生死攸关参数外部表的种类

ORACLE_LOADER:定义外部表的缺省方式,只能只念方式贯彻公文数据的装。
ORACLE_DATAPUMP:支持针对数码的装载与卸载,数据文件必须也次前进制dump文件。可以起外表表提取数额装载至内表,也可从里表卸载数据作为二进制文件填充到表面表。

3.DEFAULT DIRECTORY:缺省之目指明了表文件所在的路子

4.LOCATION:定义了标表的位置

5.ACCESS PARAMETERS:描述如何对外表表展开访问

RECORDS关键字后定义如何鉴别数据行  
DELIMITED BY
‘XXX’——换行符,常用newline定义换行,并指明字符集。对于特别的字符则用单独定义,如特殊符号,可以使用OX’十六位值’,例如tab(/t)的十六号是9,则DELIMITEDBY0X’09’;
cr(/r)的十六各项是d,那么就是DELIMITEDBY0X’0D’。
SKIP X ——跳了X行数据,有些公文被第一实行是列名,需要过了第一推行,则利用SKIP
1。
FIELDS关键字后定义如何分辨字段,常用之如下:
FIELDS:TERMINATED BY ‘x’——字段分割符。
ENCLOSED BY ‘x’——字段引用符,包含在这标志内之数都不失为一个字段。
如一行数格式如:”abc”,”a””b,””c,”。使用参数TERMINATED BY ‘,’
ENCLOSED BY
‘”‘后,系统会念到片个字段,第一个字段的价是abc,第二单字段值是a”b,”c,。
LRTRIM ——删除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某些字段空缺值都设为NULL。
于字段长度及分割符不确定还准备作外部表文件,可以使用UltraEdit、Editplus等来开展分析测试,如果文件于生,则要考虑将文件分割成多少文件并从中提取数据进行测试。

外表表对不当ACCESS的处理 

REJECT LIMIT UNLIMITED
每当开立外部表时最后进入LIMIT子句,表示足允许错误的发个数。默认值为零星。设定为UNLIMITED则错误不让限制
BADFILE和NOBADFILE子句
用于指定将捕获到之转换错误存放到谁文件。如果指定了NOBADFILE则象征忽略转换中的荒谬
一经非指定该参数,则网自动在源目录下转与外表表同名的.BAD文件BADFILE记录本次操作的结果,下次用会见于盖
LOGFILE和NOLOGFILE子句
同样在access parameters中加入LOGFILE
‘LOG_FILE.log’子句,则怀有Oracle的错误信息放入’LOG_FILE.log’中
假如NOLOGFILE子句则象征未记录错误信息到log中,如忽略该子句,系统活动在源目录下转移与表面表同名的.LOG文件
留意以下几只常见的题材
1.表表经常碰到BUFFER不足的状况,因此尽可能的增大READSIZE
2.换行符不对产生的题目。在不同之操作系统中易行符的意味方法不等同,碰到错误日志提示而是换行符问题,可以利用
UltraEdit打开,直接看十六进制
3.特定行报错时,查看带有”BAD”的日志文件,其中保存了错的数码,用记事本打开看看那里出错,是否有为外部表定义相冲突

表表的局限性 

1.SQLLDR可以指定多少付一差,即ROWS=?,
外部表却没有,这对于大数据量的导入有头不方例。
2.sqlldr errors意味同意错误的行数,外部表用REJECT LIMIT
UNLIMITED,这个效应及基本相同。
3.异部表的排非能够指定为not nullable,这样就算特别麻烦拒绝某列为空值的笔录。
4.表表不克动用continueif ,如果记录出换行的饶于难处理。

 

相关文章