oracle sqlloader

C:\Users\Administrator>sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 7月 18 13:37:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

用法: SQLLDR keyword=value [,keyword=value,...]

有效的关键字:

    userid -- ORACLE 用户名/口令
   control -- 控制文件名
       log -- 日志文件名
       bad -- 错误文件名
      data -- 数据文件名
   discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)
      skip -- 要跳过的逻辑记录的数目  (默认 0)
      load -- 要加载的逻辑记录的数目  (全部默认)
    errors -- 允许的错误的数目         (默认 50)
      rows -- 常规路径绑定数组中或直接路径保存数据间的行数
               (默认: 常规路径 64, 所有直接路径)
  bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)
    silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
    direct -- 使用直接路径                     (默认 FALSE)
   parfile -- 参数文件: 包含参数说明的文件的名称
  parallel -- 执行并行加载                    (默认 FALSE)
      file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)
commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)
  readsize -- 读取缓冲区的大小               (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
multithreading -- 在直接路径中使用多线程
 resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)

PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlload
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'.位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

 

---------------------------------------------------------------------------------------------

C:\Users\Administrator>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 7月 17 15:38:57 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:
ORA-28001: the password has expired

更改 scott 的口令
新口令:
重新键入新口令:
口令已更改

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table test as select * from emp where 1=0;

表已创建。

SQL> select * from test;

SQL> desc test;
 名称                    是否为空? 类型
 ----------------------- -------- ----------------
 EMPNO                            NUMBER(4)
 ENAME                            VARCHAR2(10)
 JOB                              VARCHAR2(9)
 MGR                              NUMBER(4)
 HIREDATE                         DATE
 SAL                              NUMBER(7,2)
 COMM                             NUMBER(7,2)
 DEPTNO                           NUMBER(2)

 

查看sqlldr控制文件

C:\Users\Administrator>type D:\\2.ctl
LOAD DATA
INFILE 'D:\\1.txt'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
(EMPNO, ENAME, JOB )

 

其中append为数据装载方式，还有其他选项：    
  a、insert，为缺省方式，在数据装载开始时要求表为空    
  b、append，在表中追加新记录    
  c、replace，删除旧记录，替换成新装载的记录    
  d、truncate，同上     

-----------------------------------------

C:\Users\Administrator>type D:\\1.txt

7369,SMITH,CLERK
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7566,JONES,MANAGER
7654,MARTIN,SALESMAN
7698,BLAKE,MANAGER
7782,CLARK,MANAGER
7788,SCOTT,ANALYST
7839,KING,PRESIDENT
7844,TURNER,SALESMAN
7876,ADAMS,CLERK
7900,JAMES,CLERK
7902,FORD,ANALYST
7934,MILLER,CLERK

 ------------------------------------------------

导入命令-------------------

C:\Users\Administrator>sqlldr scott/tiger control='d:\\2.ctl' log='d:\\2.log'

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 7月 18 13:49:18 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 13
达到提交点 - 逻辑记录计数 14

type 2.ctl

LOAD DATA
INFILE 'D:\\1.txt'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
(EMPNO, ENAME )

 

C:\Users\Administrator>sqlldr scott/tiger control='d:\\2.ctl' log='d:\\2.log'

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 7月 18 13:55:27 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 13
达到提交点 - 逻辑记录计数 14

--------------------------

跳过文本文件第一列，导入第二列

LOAD DATA
INFILE 'D:\\1.txt'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
trailing nullcols    --表的字段没有对应的值时允许为空
 (virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号

ENAME

)

 

也可以跳过2个字段

LOAD DATA
INFILE 'D:\\1.txt'
APPEND INTO TABLE TEST
FIELDS TERMINATED BY ','
trailing nullcols
 (virtual_column FILLER,v2 FILLER,job )

 

2) 建立一个控制文件 users.ctl，内容如下：

OPTIONS (skip=1,rows=128)-- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行//就是从文本第几行开始导入 ，rows就是达到多少行检查点

LOAD DATA INFILE   "users_data.csv"--指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据--的文件， 

truncate --操作类型，用 truncate table 来清除表中原有记录

INTO TABLE users-- 要插入记录的表

Fields terminated     by","-- 数据中每行记录用 "," 分隔

Optionally enclosed by '"' --数据中每个字段用 '"' 框起，比如字段中有","分隔符时  

trailing nullcols --表的字段没有对应的值时允许为空

(    virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号 

user_id number,   --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示 

user_name,   

login_times,   

last_login     DATE"YYYY-MM-DD HH24:MI:SS"-- 指定接受日期的格式，相当用 to_date() 函数转换

) 

说明：在操作类型 truncate 位置可用以下中的一值：
1) insert     --为缺省方式，在数据装载开始时要求表为空
2) append  --在表中追加新记录
3) replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

3) 执行命令：

sqlldr dbuser/dbpass@dbservice control=users.ctl

在 dbservice 指示的数据库的表 users 中记录就和数据文件中的一样了。

执行完 sqlldr 后希望能留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

第一种方式，只使用一个控制文件在这个控制文件中包含数据

1) 把 users_data.cvs 中的内容补到 users.ctl 中，并以 BEGINDATA 连接，还要把 INFILE "users_data.csv" 改为 INFILE *。同时为了更大化的说明问题，把数据处理了一下。此时，完整的 users.ctl 文件内容是：

OPTIONS (skip=1,rows=128)-- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行 

LOAD DATA INFILE *   -- 因为数据同控制文件在一起，所以用 * 表示

append      -- 这里用了 append 来操作，在表 users 中附加记录

INTOTABLE users when LOGIN_TIMES<>'8' -- 还可以用 when 子句选择导入符合条件的记录

Fields terminated    by","

trailing nullcols  (  

virtual_column FILLER,   --跳过由 PL/SQL Developer 生成的第一列序号 

user_id   "user_seq.nextval",--这一列直接取序列的下一值，而不用数据中提供的值 

user_name   "'Hi '||upper(:user_name)",--,还能用SQL函数或运算对数据进行加工处理 

login_times terminated    by",",NULLIF(login_times='NULL')--可为列单独指定分隔符 

last_login     DATE"YYYY-MM-DD HH24:MI:SS"NULLIF (last_login="NULL")-- 当字段为"NULL"时就是 NULL) 

BEGINDATA   --数据从这里开始  

,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN 

1,1,Unmi,3,2009-1-5 20:34 

2,2,Fantasia,5,2008-10-15 

3,3,隔叶黄莺,8,2009-1-2 

4,4,Kypfos, NULL,NULL

5,5,不知秋,1,2008-12-23 

2) 执行一样的命令：

sqlldr dbuser/dbpass@dbservice control=users.ctl

比如，在控制台会显示这样的信息：

C:\>sqlldr dbuser/dbpass@dbservice control=users.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

达到提交点，逻辑记录计数4
达到提交点，逻辑记录计数5

上面的控制文件包含的内容比较复杂(演示目的)，请根据注释理解每个参数的意义。还能由此发掘更多用法。

最后说下有关 SQL *Loader 的性能与并发操作

1) ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。(体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗？)

2）常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。

4) 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.

  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 就算不错了，开始可用结构 相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

（注意：一般只能用ASCII码形式，切记要转换编码，不然导入数据为空）(ftp上传csv文件的传输类型选择ascii)