分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.youkuaiyun.com/jiangjunshow
也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！
--=================
-- Oracle 外部表
--=================
外部表只能在Oracle 9i 之后来使用。简单地说，外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，我们
可以把一个操作系统文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问。外部表是对数据库表的延伸。
一、外部表的特性
位于文件系统之中，按一定格式分割，如文本文件或者其他类型的表可以作为外部表。
对外部表的访问可以通过SQL语句来完成，而不需要先将外部表中的数据装载进数据库中。
外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。
ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。
二、创建外部表的注意事项
1.需要先建立目录对象
2.对于操作系统文件的要求
文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件
3.在建立临时表时的相关限制
对表中字段的名称存在特殊字符的情况下，必须使用英文状态的下的双引号将该表列名称连接起来。如采用”SalseID#”。
对于列名字中特殊符号未采用双引号括起来时，会导致无法正常查询数据。
建议不用使用特殊的列标题字符
在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间。
创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。
简单地说，数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系。而没有存储实际的数据。
由于存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作。
4.删除外部表或者目录对象
一般情况下，先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象。
如果在未删除外部表的情况下，强制删除了目录，在查询到被删除的外部表时，将收到"对象不存在"的错误信息。
查询dba_external_locations来获得当前所有的目录对象以及相关的外部表，同时会给出这些外部表所对应的操作系统文件的名字。
5.对于操作系统平台的限制
不同的操作系统对于外部表有不同的解释和显示方式
如在Linux操作系统中创建的文件是分号分隔且每行一条记录，但该文件在Windows操作系统上打开则并非如此。
建议避免不同操作系统以及不同字符集所带来的影响
三、创建外部表
使用CREATE TABLE语句的ORGANIZATION EXTENERAL子句来创建外部表。外部表不分配任何盘区，因为仅仅是在数据字典中创建元数据。
1.外部表的创建语法
create table table_name
(col1 datatype1,col2 datatype2 ,col3 datatype3)
organization exteneral
(.....)
2.由查询结果集，使用Oracle_datapump来填充数据来生成外部表
a.创建系统目录以及Oracle数据目录名来建立对应关系，同时授予权限
[oracle@oradb ~]$ mkdir -p /home/oracle/external_tb/data
sys@ORCL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';
sys@ORCL> grant read,write on directory dat_dir to scott;
b.创建外部表
scott@ORCL> 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 )
9 parallel --按并行方式来填充
10 as
11 select ename,job,sal,dname -填充使用的原始数据
12 from emp join dept
13 on emp.deptno=dept.deptno;
c.--验证外部表
scott@ORCL> select * from ex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
WARD SALESMAN 1250 SALES
JONES MANAGER 2975 RESEARCH
..........
对于使用上述方式创建的外部表可以将其复制到其他路径作为外部表的原始数据来生成新的外部表，用于转移数据。
3.使用SQLLDR提供外部表的定义并创建外部表
关于SQL*Loader的使用请参照：SQL*Loader使用方法
我们使用SQL*Loader和下面的这个控制文件来生成外部表的定义
[oracle@oradb ~]$ cat demo1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_NEW
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE 参数有以下三个值：
NOT_USED：默认值。
EXECUTE：这个值说明SQLLDR不会生成并执行一个SQL INSERT语句；而是会创建一个外部表，且使用一个批量SQL语句来加载。
GENERATE_ONLY：使SQLLDR 并不具体加载任何数据，而只是会生成所执行的SQL DDL 和DML 语句，并放到它创建的日志文件中。
注：DIRECT=TRUE 覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，则会加载数据，而不会生成外部表。
[oracle@oradb ~]$ cat demo1.log --查看sqlldr产生的日志文件
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(20),
"LOC" VARCHAR2(20)
)
ORGANIZATION external --该子句表明是一个外部表 heap 对应普通表，index 对应iot，external 对应外部表
(
TYPE oracle_loader --说明外部文件访问方式：oracle_loader或oracle_datapump(9i不支持)
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目录
ACCESS PARAMETERS --这个访问参数有些类似于sqlldr中控制文件中的描述信息
( --系统根据这些描述信息来生成外部表的格式
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: --用于将数据填充到表，使用append方式
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"
statements to cleanup objects created by previous statements: --用于删除目录和外部表的定义信息
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
-----------------------------------------------------------------------------------------------------------------------
sys@ORCL> grant create any directory to scott;
sys@ORCL> grant drop any directory to scott;
scott@ORCL> create table dept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
scott@ORCL> select * from dept_new;
no rows selected
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute
scott@ORCL> select * from dept_new;
DEPTNO DNAME LOC
---------- -------------------- -------------------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
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 ：
&nb
给我老师的人工智能教程打call！http://blog.youkuaiyun.com/jiangjunshow
扫一扫
548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
