Oracle：SQL*Loader与外部表(External Table)的用法

exp                       : 二进制-->数据库

sqlloader              : 文本   -->数据库

oracle_loader       : 文本   -->数据库

oracle_datapump : 二进制-->数据库

===================================================================================

SQL*Loader的用法：

1. SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据   
2. 仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。现在，我们抛开其理论不谈，用实例来使   
3. 您快速掌握SQL*LOADER的使用方法。   
4. 　　首先，我们认识一下SQL*LOADER。   
5. 　　在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。   
6. 　　如执行：d:\oracle>sqlldr   
7. SQL*Loader: Release 8.1.6.0.0 - Production on 星期二 1月 8 11:06:42 2002   
8. (c) Copyright 1999 Oracle Corporation.   All rights reserved.   
9.   
10. 用法: SQLLOAD 关键字 = 值 [,keyword=value,...]   
11. 有效的关键字:   
12.      userid -- ORACLE username/password   
13.     control -- Control file name   
14.         log -- Log file name   
15.         bad -- Bad file name   
16.        data -- Data file name   
17.     discard -- Discard file name   
18. discardmax -- Number of discards to allow         (全部默认)   
19.        skip -- Number of logical records to skip   (默认0)   
20.        load -- Number of logical records to load   (全部默认)   
21.      errors -- Number of errors to allow           (默认50)   
22.        rows -- Number of rows in conventional path bind array or between direct p   
23. ath data saves   
24. （默认: 常规路径 64, 所有直接路径）   
25.    bindsize -- Size of conventional path bind array in bytes(默认65536)   
26.      silent -- Suppress messages during run (header,feedback,errors,discards,part   
27. itions)   
28.      direct -- use direct path                     (默认FALSE)   
29.     parfile -- parameter file: name of file that contains parameter specification   
30. s   
31.    parallel -- do parallel load                    (默认FALSE)   
32.        file -- File to allocate extents from   
33. skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默   
34. 认FALSE)   
35. skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus   
36. able(默认FALSE)   
37. commit_discontinued -- commit loaded rows when load is discontinued(默认FALSE)   
38.    readsize -- Size of Read buffer                 (默认1048576)   
39. PLEASE NOTE: 命令行参数可以由位置或关键字指定   
40. 。前者的例子是 'sqlload   
41. scott/tiger foo';后者的例子是 'sqlload control=foo   
42. userid=scott/tiger'.位置指定参数的时间必须早于   
43. 但不可迟于由关键字指定的参数。例如,   
44. 'SQLLOAD SCott/tiger control=foo logfile=log', 但   
45. '不允许 sqlload scott/tiger control=foo log',即使允许   
46. 参数 'log' 的位置正确。   
47. d:\oracle>   
48.      我们可以从中看到一些基本的帮助信息，这里，我用到的是中文的WIN2000　ADV　SERVER。   
49. 　　我们知道，SQL*LOADER只能导入纯文本，所以我们现在开始以实例来讲解其用法。   
50. 　　
51. 一、已存在数据源result.csv，欲倒入ORACLE中FANCY用户下。   
52. 　　　　result.csv内容：   
53. 　　1,默认 Web 站点,192.168.2.254:80:,RUNNING   
54. 　　2,other,192.168.2.254:80:test.com,STOPPED   
55. 　　3,third,192.168.2.254:81:thirdabc.com,RUNNING   
56. 　　从中，我们看出4列，分别以逗号分隔，为变长字符串。   
57. 　　二、制定控制文件result.ctl   
58.          result.ctl内容：   
59. load data   
60. infile 'result.csv'  
61. append/insert/replace into table resultxt    
62. fields terminated by ',' optionally enclosed by ' " '
    ( id, website, ipport,status )
    
63.  
64. 　　说明：   
65. 　　infile　指数据源文件　这里我们省略了默认的　discardfile result.dsc   badfile   result.bad   
66. 　　into table resultxt 默认是INSERT，也可以into table resultxt APPEND为追加方式，或REPLACE  
67. 　　terminated by ','　指用逗号分隔   
68. 　　terminated by whitespace　结尾以空白分隔   
69. 　　三、此时我们执行加载：   
70. D:\>sqlldr userid=fancy/testpass control=result.ctl log=resulthis.out  
71. SQL*Loader: Release 8.1.6.0.0 - Production on 星期二 1月 8 10:25:42 2002   
72. (c) Copyright 1999 Oracle Corporation.   All rights reserved.   
73. SQL*Loader-941:   在描述表RESULTXT时出现错误   
74. ORA-04043: 对象 RESULTXT 不存在   
75. 　　提示出错，因为数据库没有对应的表。   
76. 　　四、在数据库建立表   
77. 　   create table resultxt   
78.    (resultid varchar2(500),   
79.     website varchar2(500),   
80.     ipport varchar2(500),   
81.     status varchar2(500))   
82. /   
83. 　　五、重新执行加载   
84. 　　D:\>sqlldr userid=fancy/k1i7l6l8 control=result.ctl log=resulthis.out  
85. SQL*Loader: Release 8.1.6.0.0 - Production on 星期二 1月 8 10:31:57 2002   
86. (c) Copyright 1999 Oracle Corporation.   All rights reserved.   
87. 达到提交点，逻辑记录计数2   
88. 达到提交点，逻辑记录计数3   
89. 　　已经成功！我们可以通过日志文件来分析其过程：resulthis.out内容如下：   
90. SQL*Loader: Release 8.1.6.0.0 - Production on 星期二 1月 8 10:31:57 2002   
91. (c) Copyright 1999 Oracle Corporation.   All rights reserved.   
92. 控制文件: result.ctl   
93. 数据文件: result.csv   
94. 错误文件: result.bad   
95. 废弃文件: 未作指定   
96. :    
97. (可废弃所有记录)   
98. 装载数: ALL  
99. 跳过数: 0   
100. 允许的错误: 50   
101. 绑定数组: 64 行，最大 65536 字节   
102. 继续:     未作指定   
103. 所用路径:        常规   
104. 表RESULTXT   
105. 已载入从每个逻辑记录   
106. 插入选项对此表INSERT生效   
107.     列名                         位置       长度   中止 包装数据类型   
108. ------------------------------ ---------- ----- ---- ---- ---------------------   
109. RESULTID                             FIRST      *     ,       CHARACTER               
110. WEBSITE                               NEXT      *     ,       CHARACTER               
111. IPPORT                                NEXT      *     ,       CHARACTER               
112. STATUS                                NEXT      *   WHT       CHARACTER               
113.   
114. 表RESULTXT:    
115. 3 行载入成功   
116. 由于数据错误, 0 行没有载入。   
117. 由于所有 WHEN 子句失败, 0 行没有载入。   
118. 由于所有字段都为空的, 0 行没有载入。   
119.   
120. 为结合数组分配的空间:     65016字节（63行）   
121. 除绑定数组外的内存空间分配:          0字节   
122. 跳过的逻辑记录总数:         0   
123. 读取的逻辑记录总数:         3   
124. 拒绝的逻辑记录总数:         0   
125. 废弃的逻辑记录总数:         0   
126. 从星期二 1月   08 10:31:57 2002开始运行   
127. 在星期二 1月   08 10:32:00 2002处运行结束   
128. 经过时间为: 00: 00: 02.70   
129. CPU 时间为: 00: 00: 00.10(可   
130. 　　六、并发操作   
131. 　　sqlldr userid=/ control=result1.ctl direct=true parallel=true  
132.     sqlldr userid=/ control=result2.ctl direct=true parallel=true  
133.     sqlldr userid=/ control=result2.ctl direct=true parallel=true  
134.      当加载大量数据时（大约超过10GB），最好抑制日志的产生：   
135. 　　SQL>ALTER TABLE RESULTXT nologging;   
136.      这样不产生REDO　LOG，可以提高效率。然后在CONTROL文件中load data上面加一行：unrecoverable    
137.      此选项必须要与DIRECT共同应用。   
138. 　　在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到1－10G就算不错了，开始可用结构   
139.      相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。  

Unloader这样的工具，Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件：
　　set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
　　spool oradata.txt
　　select col1 || ',' || col2 || ',' || col3
　　from tab1
　　where col2 = 'XYZ';
　　spool off
　　另外，也可以使用使用 UTL_FILE PL/SQL 包处理:
　　rem Remember to update initSID.ora, utl_file_dir='c:/oradata' parameter
　　declare
　　fp utl_file.file_type;
　　begin
　　fp := utl_file.fopen('c:/oradata','tab1.txt','w');
　　utl_file.putf(fp, '%s, %s/n', 'TextField', 55);
　　utl_file.fclose(fp);
　　end;
　　
　　当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

　　SQL*Loader 数据的提交：
　　一般情况下是在导入数据文件数据后提交的。
　　也可以通过指定 ROWS= 参数来指定每次提交记录数。
　　提高 SQL*Loader 的性能：
　　1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。
　　2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。
　　3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。
　　4) 可以同时运行多个导入任务.
　　常规导入与direct导入方式的区别：
　　常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

 

===========================================================================================

外部表的用法：

 

 

Oracle有一种表叫外部表，允许你只读访问。外部表定义为一个表，但是不在数据库中，它的数据一般存储在操作系统文件中，数据库中只存储外部表的元数据描述。它可以像数据库中普通表一样来显示外部表的数据。外部表的数据能够直接查询或者使用并行SQL。

你可以选择，连接，排序外部表数据。还可以为外部表创建视图，同义词。但是，不可以在外部表上创建索引，执行DML操作。

 

使用CREATE TABLE...ORGANIZATION EXTERNAL语句来定义外部表的元数据。这个定义可以认为是一个视图用来通过SQL查询外部表的数据但是不加载到数据库中。实际的机制是一种访问驱动用来读取外部表的数据。当你使用外部表卸载数据时，自动的基于select语句的数据类型创建元数据。

 

Oracle为外部表提供两种访问驱动：

1.  默认的访问驱动是ORACLE_LOADER。这个允许使用Oracle loader技术从外部文件读取数据。ORACLE_LOADER访问驱动提供数据匹配能力，类似于SQL*Loader工具控制文件语法的子集。

2.  第二种访问驱动是ORACLE_DATAPUMP.这个可用让你卸载数据，从数据库中读数据，插入到外部表，变成一个或多个外部文件，可以用来重新加载到数据库中。

 

外部表是一种很有用的方法，用来执行基本的数据抽取，传输，加载，特别是用于数据仓库中。

 

 一、oracle_loader的用法:

假如有如下两个数据文件:
1: 数据文件的格式
F1.TXT文件:

13234,FIRSTS
46464,TESTA

F2.TEXT文件:

13234,SECONDS
46464,TEST
2:创建目录,并用DBA进行授权;
sql> create directory test_dir as 'E:temp';
sql>grant read,write on directory test_dir to users;
注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。

      一定要给oracle用户对这个目录可读可写的权限，操作系统层面，如使用chmod -R 777 test_dir；
3:使用被授权的用户users创建外部表:
create table test_table
(ms_no varchar(20),
tip varchar(20),
descs varchar(20))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_dir
ACCESS Parameters
(
RECORDS DELIMITED BY NEWLINE
badfile 'bad_dev.txt'
LOGFILE 'log_dev.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(ms_no,tip,descs)
)
LOCATION('F1.txt','F2.txt')
)

reject limit unlimited
;
表创建完成.当然也可以导入一个文件

4:进行SELECT 操作看是否正确;
SQL>select * from test_table
结果如下:
MS_NO TIP DESCS
-------------------- -------------------- --------------------
13234 FIRSTS
46464 TESTA
13234 SECONDS
46464 TEST

 

5. 如何得到外部表的有关信息:
SQL> DESC DBA_EXTERNAL_TABLES;
Name Type Nullable
----------------------- ------------- - ----
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
TYPE_OWNER CHAR(3) Y
TYPE_NAME VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3) Y
DEFAULT_DIRECTORY_NAME VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40) Y
ACCESS_TYPE VARCHAR2(7) Y
ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM
DBA_EXTERNAL_TABLES;
可以得到外部表的相关信息;

6. 如何得到外部路径的信息:
SQL> desc DBA_EXTERNAL_LOCATIONS;
得到该表结构:
Name Type Nullable
--------------- -------------- --------
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
LOCATION VARCHAR2(4000) Y
DIRECTORY_OWNER CHAR(3) Y
DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * from DBA_EXTERNAL_LOCATIONS;
得到具体信息;

 

7. 更改参数：

--更改拒绝限制
ALTER TABLE aa LIMIT 100;
--更改默认目录说明
ALTER TABLE aa DIRECTORY DEFAULT DIRECTORY bdump;
--修改访问参数,如分隔符由","变为"|"
ALTER TABLE aaPARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改文件位置:
ALTER TABLE aaLOCATION('TC_REG_MNGREGIONCODE.txt');
 
drop table aa;
--删除目录
drop DIRECTORY bdump;

二、oracle_datapump的用法：

可以利用ORACLE_DATAPUMP功能来迁移数据到别的用户或者数据库中。

利用带AS 子查询的语句创建一个外部表，指定location,可以把表中的数据卸载到指定的文件中。

SQL> create table manager_ext
  2  (
  3    MGRNO,
  4    MNAME
  5  )
  6  ORGANIZATION EXTERNAL
  7  (
  8  TYPE ORACLE_DATAPUMP
  9  DEFAULT DIRECTORY admin_dat_dir
 10  LOCATION('TEST.DMP')
 11  )
 12  AS SELECT ROWNUM MGRNO, MNAME  FROM manager;

Table created

Executed in 0.984 seconds

SQL> select * from manager_ext;

     MGRNO MNAME
---------- ------------------------------
         1  SMITH
         2  ALLEN.
         3  BLAKE

Executed in 0.156 seconds

这个时候就在admin_dat_dir目录下产生一个二进制文件TEST.DMP，这个文件可以被别的用户，或者数据库用来加载到外部表中，从而实现数据迁移。

 

以下示例把数据迁移到另一用户的表中。

SQL> connect user_test/test123;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as user_test

SQL> create table manager_ext_loader
  2  (
  3    MGRNO number,
  4    MNAME varchar2(30)
  5  )
  6  ORGANIZATION EXTERNAL
  7  (
  8  TYPE ORACLE_DATAPUMP
  9  DEFAULT DIRECTORY admin_dat_dir
 10  LOCATION('TEST.DMP')
 11  );

Table created

Executed in 0.016 seconds

 

SQL> select * from manager_ext_loader;

select * from manager_ext_loader

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-04074: no write access to directory object ADMIN_DAT_DIR
ORA-06512: 在 "SYS.ORACLE_DATAPUMP", line 19

授予写权限：grant write on  DIRECTORY admin_dat_dir TO user_test;

SQL> select * from manager_ext_loader;

     MGRNO MNAME
---------- ------------------------------
         1  SMITH
         2  ALLEN.
         3  BLAKE

Executed in 0.36 seconds

SQL>