实验笔记--sql*loader

最新推荐文章于 2022-03-23 16:48:01 发布

cindy-berry

最新推荐文章于 2022-03-23 16:48:01 发布

阅读量444

点赞数

分类专栏：读书笔记（开发）

本文链接：https://blog.csdn.net/u012366626/article/details/39349253

版权

读书笔记（开发）专栏收录该内容

27 篇文章 0 订阅

订阅专栏

《三思笔记》

首先linux下面

cd
vi ldr_case1.ctl
i
load data
infile *
into table bonus
fields terminated by ","
(ename,job,sal)
begindata
smith,clerk,3904
allen,salesman,2891
ward,salesman,3128
king,president,2523

(按esc)

再按:wq

然后cat ldr_case1.ctl

sqlldr scott/tiger control=ldr_case1.ctl

[oracle@cindy ~]$ sqlldr scott/tiger control=ldr_case1.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Aug 29 23:58:05 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

可以看到数据库中

SQL> select * from BONUS t;
 
ENAME      JOB              SAL       COMM
---------- --------- ---------- ----------
smith      clerk           3904 
allen      salesman        2891 
ward       salesman        3128 
king       president       2523

显然导入成功。

下面开始解释

load data

--------------------------------------------------------
infile *
into table bonus
fields terminated by ","
(ename,job,sal)
begindata

--------------------------------------------------------
smith,clerk,3904
allen,salesman,2891
ward,salesman,3128
king,president,2523

最上面的部分是标准语法，一般以此开头，并不是说一定要以此开头，load data前可以指定unrecoverable或者recoverable来控制此次加载的数据是否可以恢复，或者指定continue_load来表示继续加载。

infile:表示数据文件位置，如果值为*，表示数据就在控制文件中，这个例子里没有单独的数据文件，大多数数据文件和控制文件分离
into table tbl_name:tbl_name即数据要加载到的目录表，该表必须在加载之前就创建
into前还有命令
- insert :向表中插入数据，表必须为空
- append:向表中追加数据，表可为空可非空
- replace:替换表中的数据，相当于delete->insert
- truncate:类似replace，相当于truncate->insert
fields terminated by ","：设置数据部分字符串的分隔值，可以是其他的字符，只要表示数据分隔就行
(ename,job,sal)：要插入表的列名，这里要与表中列名完全相同，列的顺序可以与表中列的顺序不同，但是必须与数据部分的列一一对应
begindata：表示以下为待加载的数据，仅当infile指定为*时有效

如果数据文件和控制文件分开了，那么需要把*改成数据文件名即可

默认情况下，sqlldr在执行过程中会有一个日志文件产生，扩展名.log，且在相同路径下

[oracle@cindy ~]$ ls

app       Desktop        ldr??_case1.log                         rmanjiaoben
autorman  ldr_case1.ctl  p10404530_112030_Linux-x86-64_1of7.zip
database  ldr_case1.log  p10404530_112030_Linux-x86-64_2of7.zip

[oracle@cindy ~]$ cat ldr_case1.log

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Aug 29 23:58:05 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   ldr_case1.ctl
Data File:      ldr_case1.ctl
  Bad File:     ldr_case1.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table BONUS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENAME                               FIRST     *   ,       CHARACTER            
JOB                                  NEXT     *   ,       CHARACTER            
SAL                                  NEXT     *   ,       CHARACTER            


Table BONUS:
  <strong>4 Rows successfully loaded.</strong>
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri Aug 29 23:58:05 2014
Run ended on Fri Aug 29 23:58:07 2014

<strong>Elapsed time was:     00:00:01.98</strong>
CPU time was:         00:00:00.01