Oracle外部表


sqlldr是把数据加载到数据库中；外部表中的数据是基于操作系统文件的，真正的数据没有保存到数据库中，是在操作系统文件里面的。所以外部表里面的数据只可以select。外部表基本上可以替代sqlldr。但是在以下3种情况下，应该选择sqlldr而不是外部表：

1.必须通过网络加载数据，也就是说，输入文件不在数据库服务器上。

2.多个用户必须并发的使用相同的外部表来处理不同的输入文件。

3.必须使用LOB类型。外部表不支持LOG。

利用sqlldr的例子来生成外部表的脚本

  1. [Oracle@linux sqlldr]$ pwd  
  2. /u01/sqlldr  
  3. [oracle@linux sqlldr]$ cat demo1.ctl   
  4. LOAD DATA  
  5. INFILE *  
  6. INTO TABLE DEPT  
  7. FIELDS TERMINATED BY ','  
  8. (DEPTNO,DNAME,LOC)  
  9. BEGINDATA  
  10. 10,Sales,Virginia  
  11. 20,Accounting,Virginia  
  12. 30,Consulting,Virginia  
  13. 40,Finance,Virginia  
  14. ABC,XYZ,Hello  
  15. [oracle@linux sqlldr]$ sqlldr ing/ing demo1.ctl external_table=generate_only  
  16.   
  17. SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011  
  18.   
  19. Copyright (c) 1982, 2007, Oracle.  All rights reserved.  

查看生成的log文件

  1. [oracle@linux sqlldr]$ pwd  
  2. /u01/sqlldr  
  3. [oracle@linux sqlldr]$ cat demo1.log   
  4.   
  5. SQL*Loader: Release 10.2.0.4.0 - Production on 星期二 10月 4 21:59:06 2011  
  6.   
  7. Copyright (c) 1982, 2007, Oracle.  All rights reserved.  
  8.   
  9. 控制文件:      demo1.ctl  
  10. 数据文件:      demo1.ctl  
  11.   错误文件:    demo1.bad  
  12.   废弃文件:    未作指定  
  13.    
  14. (可废弃所有记录)  
  15.   
  16. 要加载的数: ALL  
  17. 要跳过的数: 0  
  18. 允许的错误: 50  
  19. 继续:    未作指定  
  20. 所用路径:       外部表  
  21.   
  22. 表 DEPT,已加载从每个逻辑记录  
  23. 插入选项对此表 INSERT 生效  
  24.   
  25.    列名                        位置      长度  中止 包装数据类型  
  26. ------------------------------ ---------- ----- ---- ---- ---------------------   
  27. DEPTNO                              FIRST     *   ,       CHARACTER              
  28. DNAME                                NEXT     *   ,       CHARACTER              
  29. LOC                                  NEXT     *   ,       CHARACTER              
  30.   
  31.   
  32.   
  33. 文件需要 CREATE DIRECTORY 语句  
  34. ------------------------------------------------------------------------   
  35. CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/sqlldr'  
  36. --sqlldr连接到数据库，查询数据字典。没有找到合适的目录，就创建一个SYS_SQLLDR_XT_TMPDIR_00000目录。   
  37.   
  38. 用于外部表的 CREATE TABLE 语句:  
  39. ------------------------------------------------------------------------   
  40. CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"      --创建一张表SYS_SQLLDR_X_EXT_DEPT   
  41. (  
  42.   "DEPTNO" NUMBER(10),  
  43.   "DNAME" VARCHAR2(20),  
  44.   "LOC" VARCHAR2(20)  
  45. )  
  46. ORGANIZATION external           --表明这不是一张普通表，而是一张外部表。   
  47. (  
  48.   TYPE oracle_loader            --加载数据的类型。另外还有一个10G才支持的ORACLE_DATAPUMP可以用来加载或卸载数据。   
  49.   DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000            --使用前面创建的目录SYS_SQLLDR_XT_TMPDIR_00000   
  50.   ACCESS PARAMETERS   
  51.   (  
  52.     RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK    --记录默认也换行符结束。   
  53.     BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'      --在刚创建的目录中记录一个坏文件，无法加载的记录会记录到此文件中。   
  54.     LOGFILE 'demo1.log_xt'                                --日志文件。   
  55.     READSIZE 1048576                                      --加载数据的缓存区大小。1024*1024=1048576   
  56.     SKIP 6                                                --跳过前面6行不加载，因为前面6行是sqlldr控制文件的信息。   
  57.     FIELDS TERMINATED BY "," LDRTRIM                      --数据以逗号分隔，LDRTRIM表示去掉前后的空白字符。   
  58.     REJECT ROWS WITH ALL NULL FIELDS                      --外部表会在坏文件中记录全空的行，而不加载这些行。   
  59.     (  
  60.       "DEPTNO" CHAR(255)  
  61.         TERMINATED BY ",",  
  62.       "DNAME" CHAR(255)  
  63.         TERMINATED BY ",",  
  64.       "LOC" CHAR(255)  
  65.         TERMINATED BY ","  
  66.     )  
  67.   )  
  68.   location   
  69.   (  
  70.     'demo1.ctl'        --告诉oracle所加载的文件的文件名。   
  71.   )  
  72. )REJECT LIMIT UNLIMITED  
  73.   
  74.   
  75. 用于加载内部表的 INSERT 语句:  
  76. ------------------------------------------------------------------------   
  77. INSERT /*+ append */ INTO DEPT   --用于从外部表本身直接加载数据。    
  78. (  
  79.   DEPTNO,  
  80.   DNAME,  
  81.   LOC  
  82. )  
  83. SELECT   
  84.   "DEPTNO",  
  85.   "DNAME",  
  86.   "LOC"  
  87. FROM "SYS_SQLLDR_X_EXT_DEPT"  
  88.   
  89.   
  90. 用于清除由以前的语句创建的对象的语句:  
  91. ------------------------------------------------------------------------   
  92. DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"            --删除外部表。   
  93. DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000     --删除目录。   
  94.   
  95.   
  96.   
  97. 从 星期二 10月 04 21:59:06 2011 开始运行  
  98. 在 星期二 10月 04 21:59:06 2011 处运行结束  
  99.   
  100. 经过时间为: 00: 00: 00.14  
  101. CPU 时间为: 00: 00: 00.04  
  • 1
  • 2
  • 下一页

相关内容