- 1、表结构如下：
- 2、要导入的文件（infile），是由log分析得来：
- 3、sqlloader的controlfile如下：
- 4、现在来导入并看一下产生的log：
- 5、最后，看看导入的数据有没有问题：
今天为开发数据库导入数据，使用sqlloader。记录一下遇到的问题及笔记。
1、表结构如下：
oss@WINKSDB> desc member_actions;
Name Null? Type
----------------- -------- ------------
PHONE NOT NULL VARCHAR2(50)
ACTION_TIME NOT NULL DATE
URL VARCHAR2(80)
APP VARCHAR2(20)
ACTIVE_LEVEL VARCHAR2(10)
2、要导入的文件（infile），是由log分析得来：
[oracle@devdb: ~]$ll -th deactive_actionlist.2009-09-23
-rw-r--r-- 1 oracle oinstall 49M Sep 24 03:11 deactive_actionlist.2009-09-23
[oracle@devdb: ~]$head !$
head deactive_actionlist.2009-09-23
2009-09-23 00:00:04,314 135****8333 user/registeruser comm deactive
2009-09-23 00:00:17,053 135****3585 user/registeruser comm deactive
2009-09-23 00:02:05,704 138****3712 user/registeruser comm deactive
2009-09-23 00:02:41,097 136****1107 user/registeruser comm deactive
2009-09-23 00:02:43,330 135****6592 user/registeruser comm deactive
2009-09-23 00:04:18,818 187****7004 user/registeruser comm deactive
2009-09-23 00:05:52,878 151****6277 user/registeruser comm deactive
2009-09-23 00:09:14,438 134****9551 user/registeruser comm deactive
2009-09-23 00:09:37,988 159****9392 user/registeruser comm deactive
2009-09-23 00:09:46,555 158****8127 user/registeruser comm deactive
3、sqlloader的controlfile如下：
load data --控制文件标识
infile 'deactive_actionlist.2009-09-23' --要导入数据的文件名
truncate into table MEMBER_ACTIONS --truncate table MEMBER_ACTIONS_IMP后，再导入数据
fields terminated by " " optionally enclosed by '"' --字符终止于“空格”，并附上"双引号
(ACTION_TIME POSITION(1:19) date "YYYY-MM-DD HH24:MI:SS", --infile中的日期／时间精确到千分秒，而ACTION_TIME列 为DATE型
field2 FILLER, --所以多了一段3位的千分秒，要把它漏过去
phone,
url,
app,
active_level
) --定义列对应顺序
说明：
INSERT：为缺省方式，在数据装载开始时要求表为空。
APPEND：在表中追加新记录。
REPLACE：使用一种传统DELETE语句；因此，如果要加载的表中已经包含许多记录，这个操作可能执行得很慢。
TRUNCATE：则不同，它使用TRUNCATE SQL命令，通常会更快地执行，因为它不必物理地删除每一行。
4、现在来导入并看一下产生的log：
[oracle@devdb: ~]$sqlldr oss/oss control=deactive.ctl direct=true ---导入性能果然很快！只用了5.75秒。
[oracle@devdb: ~]$cat deactive.log
SQL*Loader: Release 11.1.0.7.0 - Production on Fri Sep 25 11:02:59 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: deactive.ctl
Data File: deactive_actionlist.2009-09-23
Bad File: deactive_actionlist.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table MEMBER_ACTIONS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ACTION_TIME 1:19 19 WHT O(") DATE YYYY-MM-DD HH24:MI:SS
FIELD2 NEXT * WHT O(") CHARACTER
(FILLER FIELD)
PHONE NEXT * WHT O(") CHARACTER
URL NEXT * WHT O(") CHARACTER
APP NEXT * WHT O(") CHARACTER
ACTIVE_LEVEL NEXT * WHT O(") CHARACTER
Table MEMBER_ACTIONS:
821263 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.
Date conversion cache disabled due to overflow (default size: 1000)
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 821263
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 195
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Fri Sep 25 11:02:59 2009
Run ended on Fri Sep 25 11:03:05 2009
Elapsed time was: 00:00:05.75
CPU time was: 00:00:03.26
5、最后，看看导入的数据有没有问题：
oss@WINKSDB> select count(*) from member_actions;
COUNT(*)
----------
821263
oss@WINKSDB> alter session set nls_date_format='yyyy-dd-mm hh24:mi:ss';
Session altered.
oss@WINKSDB> col phone for a15
oss@WINKSDB> col url for a30
oss@WINKSDB> col app for a15
oss@WINKSDB> select * from member_actions where rownum <= 10;
PHONE ACTION_TIME URL APP ACTIVE_LEVEL
--------------- ------------------- ------------------------------ --------------- ---------------
158****0739 2009-23-09 23:21:16 specialwinks comm deactive
158****8114 2009-23-09 23:21:16 winks/show comm deactive
158****6605 2009-23-09 23:21:16 winks/show comm deactive
134****5440 2009-23-09 23:21:16 message comm deactive
132****9996 2009-23-09 23:21:16 winks/show comm deactive
151****9347 2009-23-09 23:21:16 specialwinks comm deactive
139****8854 2009-23-09 23:21:16 specialwinks comm deactive
158****8584 2009-23-09 23:21:16 winks/show comm deactive
138****6827 2009-23-09 23:21:16 specialwinks comm deactive
151****1746 2009-23-09 23:21:16 config comm deactive
10 rows selected.
导入的数据没有问题！
该服务器的CPU为1颗4核的Intel(R) Xeon(R) CPU E5410 @ 2.33GHz 内存：4G
– The End –