ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 测试TOM=SQLLOADER1

测试TOM=SQLLOADER1

原创 Linux操作系统 作者:oracle_db 时间:2012-05-11 14:54:42 0 删除 编辑
测试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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    306141