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数据目录名来建立对应关系，同时授予权限
[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data
SQL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';
SQL> alter user scott account unlock identified by scott;
b.创建外部表
SQL>create table ex_tb1 --创建外部表
2 (ename,job,sal,dname) --表列描述，注意未指定数据类型
3 organization external
4 (
5 type oracle_datapump --使用datapump将查询结果填充到外部表,注,此处由select生成，故不支持
oracle_loader
6 default directory dat_dir --指定外部表的存放目录
7 location('tb1.exp，tb2.exp'))
8 parallel 2 --按并行方式来填充，这里的并行度必须与生成的文件数量一致才能起作用，详细算法可
9 as 以参看http://czmmiao.iteye.com/blog/1268453
10 select ename,job,sal,dname --填充使用的原始数据
11 from emp join dept
12 on emp.deptno=dept.deptno
c.验证外部表
SQL> select * from ex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
..................................
MILLER CLERK 1300 ACCOUNTING
对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。
3.使用SQLLDR提供外部表的定义并创建外部表
关于SQL*Loader的使用请参照：SQL*Loader使用方法
我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义
$ cat demo1.ctl
LOADDATA
INFILE*
INTOTABLEDEPT_NEW
FIELDS TERMINATEDBY','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
赋予相应的权限和创建表
SQL>grant create any directory to scott;
SQL>grant drop any directory to scott;
SQL>create table dept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
执行sqlldr命令
$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE参数有以下三个值：
NOT_USED：默认值。
EXECUTE：这个值说明SQLLDR不会生成并执行一个SQLINSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。
GENERATE_ONLY：使SQLLDR并不具体加载任何数据，而只是会生成所执行的SQL DDL和DML语句，并放到它创建的日志文件中。
注：DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。
$ cat demo1.log --查看sqlldr产生的日志文件
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 20 17:45:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: demo1.ctl
Data File: demo1.ctl
Bad File: demo1.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table DEPT_NEW, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle' --创建目录对象的语句
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader --指定外部表的访问方式，9i不支持oracle_datapump
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS --配置外部表参数
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --记录以换行为结束
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放处理失败的记录文件描述
LOGFILE 'demo1.log_xt' --日志文件
READSIZE 1048576 --Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式
则从SGA分配
SKIP 6 --跳过的记录数，因为我们使用了控制文件，所以前面的控制信息需要跳过
FIELDS TERMINATED BY "," LDRTRIM --描述字段的终止符
REJECT ROWS WITH ALL NULL FIELDS --所有为空值的行被跳过并且记录到bad file.
( --下面是描述外部文件各个列的定义
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo1.ctl' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED --描述允许的错误数，此处为无限制
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT_NEW"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW" --用于删除目录和外部表的定义信息
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Sun Nov 20 17:45:36 2011
Run ended on Sun Nov 20 17:45:37 2011
Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.05
4.使用平面文件定义并生成外部表
a.平面文件数据
1.dat：
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
2.dat：
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
$ pwd
/home/oracle/external_tb/data
$ ls
1.dat 2.dat dat_dir:tb_test.exp EMP_NEW_3198.log EMP_NEW_3413.log EX_TB1_3021.log
验证外部表
SQL> select * from emp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
....................................................................
外部表不能执行DML
SQL> delete from emp_new;
delete from emp_new
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
oracle外部表的简单使用：
外部表的含义：
外部表使用限制：（来源于http://www.examda.com/oracle/zonghe/20101031/141746967.html）
对于操作系统文件的限制：
另外在外部表格中，不能够带有标题信息。如现在有一张表格，以逗号分隔。而在其第一列数据中有各个列的标题信息。而数据库系统在连接这个表的时 候，会将这些标题信息当作普通的纪录来对待。即会将这些信息也显示在外部表中。为此如果这个标题信息与外部表的字段类型不一致（如字段内容是number 数据类型，而标题信息则是字符型数据，则在查询时就会出错）。如果数据类型恰巧一致的话，这个标题信息Oracle数据库也会当作普通记录来对待。如在建 立外部表的时候，最好确认一下操作系统文件中是否包含标题信息。如果有的话，需要删除。否则的话，可能会出错。
最后需要说明的是，当Oracle数据库系统访问这个操作系统文件的时候，会在这个文件所在的目录自动创建一个日志文件。无论最后是否访问成功，这个日志文件都会如期建立。查看这个日志文件，可以了解数据库访问外部表的频率、是否成功访问等等。
其次，这个外部表毕竟与内部表不同。在创建外部表的时候，其实在数据库中跟本没有创建表。也就是说，不会为外部表分配任何的存储空间。创建外部 表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。简单地说，数据库存储的只是与外部文件的一种对 应关系，如字段与字段的对应关系。而没有存储实际的数据。为此在表的操作与管理上，就会受到很大的限制。如在外部表上，是不能够为表创建索引。因为创建索 引就意味着要存在对应的索引记录。而外部表其实在数据库不会有存储。故在外部中是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不 受支持”的错误提示。同样的道理，在数据库中也不能够更新外部表中的数据，如插入记录、删除记录或者更新信息等等。简而言之，这个外部表对于数据库来说， 是只读的，不可更新。
要了解这个信息，则可以通过查询dba_external_locations。通过查询这张表，系统会反映当前所有的目录对象以及相关的外部 表，还会查询出这些外部表所对应的操作系统文件的名字。先查询这张表格，确定要删除的对象没有其他关联的外部表时，再进行删除。否则的话，需要先确认其他 外部表的可用性。免得因为误删除而导致外部表无法正常使用。
create or replace directory dir_bdump as 'D:\oracle\product\10.2.0\admin\fgisdb\bdump';
--查询目录
select * from dba_directories;
--查询外部表的目录
select * from dba_external_locations;
--查询外部表
select * from dba_external_tables;
--创建外部表
create table alert_fgisdb ( text varchar2(400) )
organization external (
type oracle_loader
default directory bdump
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location('alert_fgisdb.log')
)
reject limit unlimited
--通过外部表查找数据库的运行信息
to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
round((start_time-last_time)*24*60,2) mins_down,
round((last_time-lag(start_time)over(order by r)),2)days_up,
case when (lead(r) over (order by r) is null) --lead函数用于取出后N行数据
then round((sysdate-start_time),2)
end days_still_up
from (
select r,
to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
from (
select r,
text,
lag(text,1)over(order by r) start_time,--lag函数用于取出前n行数据
lag(text,2)over(order by r) last_time
from (
select rownum r,text
from alert_fgisdb
where text like'____:_:_20_'
or text like'starting oracle instance%'
)
)
where text like 'starting oracle instance%'
)
lag函数语法：lag（字段，n）
lead函数语法与lag一样。
--更改拒绝限制
ALTER TABLE alert_fgisdb LIMIT 100;
--更改默认目录说明
ALTER TABLE alert_fgisdb DIRECTORY DEFAULT DIRECTORY bdump;
--修改访问参数,如分隔符由","变为"|"
ALTER TABLE alert_fgisdb PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改文件位置:
ALTER TABLE alert_fgisdb LOCATION('TC_REG_MNGREGIONCODE.txt');
drop table alert_fgisdb;
--删除目录
drop DIRECTORY bdump;
--查询外部表（找出alert中含有ora的所有记录）
select * from alert_fgisdb where text like 'ORA-%';
--准备一个简单的select语句向这个目录中卸载数据
create table all_objects_unload
organization external
(type oracle_datapump
default directory dir_dp
location('allobjects.dat')) --allobjects.dat文件在dir_dp目录下
as select * from all_objects
--将allobjects.dat文件拷到要加载该表的机器，使用如下语句抽取DDL重建这个表
select dbms_metadata.get_ddl('TABLE','ALL_OBJECTS_UNLOAD') from dual;
--抽取后的DDL语句如下：
CREATE TABLE "GWM"."ALL_OBJECTS_UNLOAD"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DIR_DP"
LOCATION
( 'allobjects.dat'
)
)
--重建该表后，执行如下语句就可以加载这个表的信息
insert /*+ append */ into some_table select * from all_objects_unload;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30336782/viewspace-2097369/ ，如需转载，请注明出处，否则将追究法律责任。
