SQLLOADER 1普通装载

最新推荐文章于 2020-09-18 01:09:09 发布

cnk45946

最新推荐文章于 2020-09-18 01:09:09 发布

阅读量327

点赞数 1

文章标签：数据库操作系统

一描述

此档是sqlloader学习渐进的第一篇.练习使用在全局指定FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' .对源数据进行变形,对比查看数据库中的最终插入结果.

二操作环境

1.系统数据库环境:
windows server 2003 32bit + oracle db 9.0.1

2.sqlloader目录结构
E:\sqlloader_exec\ppt_case1

三过程设计

1.准备的源数据,

10,Sales,""""USA""""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia
60,"Finance",,Virginia

70,Sales,",""USA"","

80,Sales,",""USA"","

90,Sales,"","""USA""",""
95,Sales,"""""""USA"""""""

2.sqlloader控制文件ppt_case1.ctl内容

LOAD DATA
INFILE *
INTO TABLE DEPT_P1
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA

10,Sales,""""USA""""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia
60,"Finance",,Virginia

70,Sales,",""USA"","

80,Sales,",""USA"","

90,Sales,"","""USA""",""
95,Sales,"""""""USA"""""""

3.以scott用户连接数据库,创建目的表 scott.dept_p1
4.在cmd命令下执行sqlldr加载命令
5.查看sqlldr日志信息
6.查看sqlldr bad日志信息
7.查看数据库导入成功数据

四详细步骤操作

1.创建scott.dept_p1表
E:\sqlloader_exec\ppt_case1>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期六 8月 4 14:53:24 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> create table dept_p1 as select * from dept where 1=2;

表已创建。

SQL> exit
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开

E:\sqlloader_exec\ppt_case1>

2.在cmd命令下执行sqlldr加载命令
E:\sqlloader_exec\ppt_case1>sqlldr "scott/tiger" control=ppt_case1.ctl

SQL*Loader: Release 9.0.1.1.1 - Production on 星期六 8月 4 14:56:40 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

达到提交点，逻辑记录计数9
达到提交点，逻辑记录计数10

E:\sqlloader_exec\ppt_case1>

3.查看sqlldr日志
SQL*Loader: Release 9.0.1.1.1 - Production on 星期六 8月 4 14:56:40 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

控制文件: ppt_case1.ctl
数据文件: ppt_case1.ctl
错误文件: ppt_case1.bad
废弃文件: 未作指定
:
(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
绑定数组: 64 行，最大 256000 字节
继续:    未作指定
所用路径:       常规

表DEPT_P1
已加载从每个逻辑记录
插入选项对此表TRUNCATE生效

   列名                        位置      长度中止包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *    , O (") CHARACTER
DNAME                                NEXT     *    , O (") CHARACTER
LOC                                  NEXT     *    , O (") CHARACTER

记录 1: 被拒绝 - 表 DEPT_P1 的列 LOC 出现错误。
TERMINATED 和 ENCLOSED 字段后没有终止定界符

表DEPT_P1:
9 行加载成功
由于数据错误, 1 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。

为结合数组分配的空间:    49536字节（64行）
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:        0
读取的逻辑记录总数:       10
拒绝的逻辑记录总数:        1
废弃的逻辑记录总数:        0

从星期六 8月 04 14:56:40 2012开始运行
在星期六 8月 04 14:56:40 2012处运行结束

经过时间为: 00: 00: 00.08
CPU 时间为: 00: 00: 00.02(可?

4.查看sqlldr bad日志信息
10,Sales,""""USA""""

5.查看数据库导入成功数据
E:\sqlloader_exec>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期六 8月 4 15:00:16 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select * from dept_p1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 Accounting     Virginia,USA
        30 Consulting     Virginia
        40 Finance        Virginia
        50 Finance
        60 Finance
        70 Sales          ,"USA",
        80 Sales          ,"USA",
        90 Sales
        95 Sales          """USA"""

已选择9行。

SQL>

五个人总结

1.FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 源与导入之后的不同之处

源数据的内容

10,Sales,""""USA""""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia
60,"Finance",,Virginia

70,Sales,",""USA"","

80,Sales,",""USA"","

90,Sales,"","""USA""",""
95,Sales,"""""""USA"""""""

加载进数据库的数据

        20 Accounting     Virginia,USA
        30 Consulting     Virginia
        40 Finance        Virginia
        50 Finance
        60 Finance
        70 Sales          ,"USA",
        80 Sales          ,"USA",
        90 Sales
        95 Sales          """USA"""

未加载入数据库的数据为

10,Sales,""""USA""""

载入数据库中与源数据不同的数据

20,Accounting,"Virginia,USA"
50,"Finance","",Virginia
60,"Finance",,Virginia
70,Sales,",""USA"","
80,Sales,",""USA"","
90,Sales,"","""USA""",""
95,Sales,"""""""USA"""""""

2.总结

sqlldr控制文件中的OPTIONALLY ENCLOSED BY '"' 选项,当'""成多对出现时,如示例源数据中的70,80,90,95行,sqlldr会选择最外的一对'"'做为分隔符,其内的其它'""要两两成对出现才不会出错.即:当多对连续的'""出现时,要成奇数对才会加载正常.示例源数据中10号行因为成对'"'为偶数,不符合加载规则,因此被打入bad文件中去了.

ps.个人的力量有限,大家集体的实验结果无穷限. thanks.