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

姚海芮

　　exp                       : 二进制-->数据库
　　sqlloader              : 文本   -->数据库
　　oracle_loader       : 文本   -->数据库
　　oracle_datapump : 二进制-->数据库
　　
　　===================================================================================
　　SQL*Loader的用法：

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

　　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>