测试TOM=SQLLOADER1

本文详细介绍SQLLoader工具的使用方法,包括如何创建控制文件来描述数据信息,并通过具体实例演示数据加载过程及注意事项。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

测试SQLLOADER使用,它是一个加载数据的工具,
==============================================================
以下情况测试数据控制文件中包含数据的情况下,如何使用SQLLOADER,如何写控制文件

会话1:建立目标表,方便向其加载数据

SQL> conn scott/scott
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SCOTTBK
TEST7
T
DEGITS
TEST

9 rows selected.

SQL> 
SQL> create table dept_load
  2  (deptno number(2) constraint dept_load_pk primary key,
  3  dname varchar2(14),
  4  loc varchar2(13)
  5  )
  6  /

Table created.

会话2:创建控制文件,用来描述输入数据的信息

[oracle@oraclelinux ~]$ more dept_load.ctl
LOAD DATA
INFILE *
INTO TABLE dept_load
FIELDS TERMINATED BY ','==指定数据之间的分割符号是逗号
(
deptno
,dname
,loc
)
BEGINDATA
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
[oracle@oraclelinux ~]$ 

会话2:命令行方式导入数据
[oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 11 15:09:10 2012

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

Commit point reached - logical record count 4
[oracle@oraclelinux ~]$ 
检测数据
SQL> show user;
USER is "SCOTT"
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
SCOTTBK
TEST7
T
DEGITS
TEST
DEPT_LOAD

10 rows selected.

SQL> select * from dept_load;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> 
刚才控制文件中的4行数据被加载,默认情况下加载选项是INSERT不是APPEND.如果目标表不为空,会出错

当执行完以后,不要忘记看加载过程日志,它的默认目录和控制文件在同一目录下,执行完导入后自动生成,里边记录了关于导入过程的详细信息

[oracle@oraclelinux ~]$ ls -ltr dept*
-rw-r--r--  1 oracle oinstall  178 May 11 15:02 dept_load.ctl
-rw-r--r--  1 oracle oinstall 1545 May 11 15:09 dept_load.log
[oracle@oraclelinux ~]$ 

[oracle@oraclelinux ~]$ more dept_load.log 
SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 11 15:09:10 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Control File:   dept_load.ctl Data File:      dept_load.ctl   Bad File:     dept_load.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 DEPT_LOAD, loaded from every logical record. Insert option in effect for this table: INSERT
   Column Name                  Position   Len  Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO                              FIRST     *   ,       CHARACTER             DNAME                                NEXT     *   ,       CHARACTER             LOC                                  NEXT     *   ,       CHARACTER            

Table DEPT_LOAD:   4 Rows successfully loaded.   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 May 11 15:09:10 2012 Run ended on Fri May 11 15:09:10 2012
Elapsed time was:     00:00:00.55 CPU time was:         00:00:00.08 [oracle@oraclelinux ~]$ 
==============================================================

下面测试 数据中有简单分割符的情况
会话1.清空DEPT_LOAD

SQL> truncate table dept_load;

Table truncated.
修改控制文件
LOAD DATA
INFILE *
INTO TABLE dept_load
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
deptno
,dname
,loc
)
BEGINDATA
10,sales,"abc,usa"
20,aaa,"xx,""yy"""
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
重新导入数据
SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 11 15:57:01 2012

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

Commit point reached - logical record count 4
检测数据

oracle@oraclelinux ~]$ sqlldr userid=scott/scott control=dept_load2.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri May 11 16:03:42 2012

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

Commit point reached - logical record count 5
[oracle@oraclelinux ~]$ exit
exit

SQL> select * from dept_load2;
select * from dept_load2
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> show user;
USER is "SCOTT"
SQL> select * from dept_load;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 sales          abc,usa
        20 aaa            xx,"yy"
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select count(*) from dept_load;

  COUNT(*)
----------
         4

SQL> 
注意:数据中如果有逗号,则需要把数据用“”引起来,如果数据中有“”那么需要把数据用“”括起来,““被翻译成一个”



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-723351/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-723351/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值