测试SQLLOADER使用,它是一个加载数据的工具,
==============================================================
以下情况测试数据控制文件中包含数据的情况下,如何使用SQLLOADER,如何写控制文件
会话2:创建控制文件,用来描述输入数据的信息
会话2:命令行方式导入数据
会话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 ~]$
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/