Oracle 外部表

最新推荐文章于 2024-07-31 21:21:45 发布

weixin_30608503

最新推荐文章于 2024-07-31 21:21:45 发布

阅读量94

点赞数

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/fdz407881772/p/4885330.html

版权

Oracle外部表

定义：
外部表只能在Oracle9i之后来使用。外部表，是指不存在于数据库中的表。通过向Oracle提供描述外部表的元数据，可以把操作系统上的文件当成一个只读的数据库表，就像这些数据存储在一个普通数据库表中一样来进行访问，外部表是对数据库表的延伸。

特性/限制：
（1）位于文件系统之中，是操作系统文件，按一定格式分割，如文本文件，要有固定的格式、不能有标题列、访问时会自动创建一个日志文件；
（2）对外部表的访问直接通过SQL语句来完成，不需要先存储在数据库中，创建外部表的时候，不会为外部表分配任何的存储空间,数据库存储的只是与外部文件的一种对应关系，如字段与字段的对应关系,而没有存储实际的数据；
（3）外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引；
（4）ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据；
（5）可以查询操作和连接，可以并行操作。
（6）删除时，要先删除外部表，然后再删除目录对象，如果目录对象中有多个表，应删除所有表之后再删除目录对象;

外部表存储：
外部表文件存储于文件服务器上，需要先设置外部表路径信息：

--数据库中创创建外部路径：
create or replace directory CHL_TEST as '/app/dv21/work/share/appldat/chl_test';
grant read,write on directory CHL_TEST to roapps;
--查询维护的路径：
SELECT * FROM all_directories ad WHERE ad.directory_name = 'CHL_TEST';

路径维护后，应该确保文件服务器上有相应的文件路径，不然后续文件操作会报错！！

对外部文件常用操作：复制/删除/判断是否存在:

DECLARE
  w_file_exist     BOOLEAN;
  w_file_length    NUMBER;
  w_file_size      BINARY_INTEGER;
  l_src_file_dir   VARCHAR2(240) := 'XXIF_INPUT';
  l_src_file_name  VARCHAR2(240) := 'ERP_IFAST2ERP003.dat';
  l_dest_file_dir  VARCHAR2(240) := 'CHL_TEST';
  l_dest_file_name VARCHAR2(240) := 'CHLTEST.dat';
BEGIN
  --判断是否存在
  utl_file.fgetattr(location    => l_dest_file_dir,
                    filename    => l_dest_file_name,
                    fexists     => w_file_exist,
                    file_length => w_file_length,
                    block_size  => w_file_size);
  IF w_file_exist THEN
    dbms_output.put_line(w_file_length);
    dbms_output.put_line(w_file_size);
    --删除
    utl_file.fremove(location => l_dest_file_dir,
                     filename => l_dest_file_name);
  END IF;
  --复制
  utl_file.fcopy(src_location  => l_src_file_dir,
                 src_filename  => l_src_file_name,
                 dest_location => l_dest_file_dir,
                 dest_filename => l_dest_file_name);
  --重命名文件，有移动的意思，如果目标文件存在且选择不重写就会报错
  utl_file.frename(src_location  => l_dest_file_dir,
                   src_filename  => l_dest_file_name,
                   dest_location => l_dest_file_dir,
                   dest_filename => 'CHLTEST1.dat',
                   overwrite     => TRUE);
END;

创建外部表：
主要通过ORGANIZATION EXTERNAL关键字来关联数据表字段和外部文件字段:

CREATE TABLE CHL_TEST
(
USER_ID NUMBER,
USER_NAME VARCHAR2(80),
ADDRESS VARCHAR2(240),
PHONE VARCHAR2(18),
COMMENTS VARCHAR2(2000)
)
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY CHL_TEST
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE 
STRING SIZES ARE IN BYTES
BADFILE XXIF_LOG:'CHL_TEST_%p_%a.bad'
DISCARDFILE XXIF_LOG:'CHL_TEST_%p_%a.disc'
LOGFILE XXIF_LOG:'CHL_TEST_%p_%a.log'
READSIZE 1048576
DATE_CACHE 1000
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
USER_ID CHAR(2000),
USER_NAME CHAR(2000),
ADDRESS CHAR(2000),
PHONE CHAR(2000),
COMMENTS CHAR(2000)
)
)
LOCATION (CHL_TEST:'CHLTEST.dat')
)
REJECT LIMIT 0;
--授权
GRANT SELECT ON CHL_TEST TO ROAPPS;

查看外部表与外部文件的联系：

SELECT * FROM all_external_locations;
SELECT * FROM user_external_locations;
SELECT * FROM dba_external_locations;

参数信息:
--注释不能直接写在创表的语句中，中断语句会报错
Log文件中使用了_%p_%a:
%p is replaced by the process ID of the current process.
%a is replaced by the agent number of the current process.
RECORDS DELIMITED BY NEWLINE--记录默认以换行符结束
CHARACTERSET ZHS16GBK --字符集
STRING SIZES ARE IN BYTES --字符串统计方式，STRING SIZES ARE IN BYTES
BADFILE XXIF_LOG:'CHL_TEST_%p_%a.bad' --存放处理失败的记录文件描述
DISCARDFILE XXIF_LOG:'CHL_TEST_%p_%a.disc'
LOGFILE XXIF_LOG:'CHL_TEST_%p_%a.log' --日志文件
READSIZE 1048576--ORACLE读取输入数据文件所用的默认缓冲区,此处为MB
DATE_CACHE 1000
FIELDS TERMINATED BY ',' --列划分符号
OPTIONALLY ENCLOSED BY '"'--数据被什么包围
LOCATION (CHL_TEST:'CHLTEST.dat')--文件位置：名称
REJECT LIMIT 0 --允许错误数据条数，设置为0，不允许有错误数据，否则报错;

测试：
文件中添加四条记录，内容如下：

"10001","Chang","Shanxi","1234567890","CHLTEST",
"10002","Wang","Xian","4242525","CHLTEST",
"10003","Bai","Shangluo","64636346754","",
"10004","Zhao","Beijing","643636556","CHLTEST",

保存文件为CHLTEST.dat后上传到指定路径；
从表中查询，结果能查出文件中的四条数据：

SELECT * FROM chl_test;
文件中添加一条错误数据：
"Zhao","Beijing","643636556","CHLTEST",
然后再执行查询直接会报错ORA-29913：
把表创建语句REJECT LIMIT 改为1，则可以容忍一条错误的数据，可以不报错，查到四条正确的数据；
前往Log文件路径，能发现错误文件和日志文件，可以看到里面错误的数据和日志信息；
由于不能直接操作外部表，一般需要把外部表数据放在数据库表中，再进行其他操作！

转载于:https://www.cnblogs.com/fdz407881772/p/4885330.html