Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1010795
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-03-31 20:47:03

Use sql loader to load data into database.
分类: ORACLE11G English ORACLE 31人阅读 评论(0) 举报

SQL Loader is the utility to use for high performance data loads. 

The SQL Loader control file contains information that describes how the data will be loaded ,Which contains the table name, column datatypes, field delimiters, etc.

See example:

$more account_data.csv

8,"DZcLHm6H2iDNnvjU7TafFA==","53746a61-aeaa-4856-ba52-932fbb6b8161","nZAylFNJOOY=","DZcLHm6H2iDNnvjU7TafFA==",0,"2010-08-12",90,1
9,"5nBoqMWLF+w=","53746a61-aeaa-4856-ba52-932fbb6b8190","vpluH45reHw=","QS9PWc3XtBY83LkgSkGJJA==",0,"2010-08-12",90,1

 $ more account.ctl
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=1)
LOAD DATA
INFILE '/henry/account_data.csv'
APPEND INTO TABLE account
FIELDS TERMINATED BY ',' Optionally enclosed by '"'
(
id ,
  field3 ,
  field4  ,
  field2  ,
  field1  ,
  field5 ,
  field6 date 'yyyy-mm-dd',
  field7  ,
  field8
)

then run the command from bash:

sqlldr userid=pv/pv  control=account_data.ctl log=account_data.log

view account_data.log


SQL*Loader: Release 11.2.0.2.0 - Production on Tue Feb 22 22:15:41 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   account_data.ctl
Data File:     /henry/account_data.csv
  Bad File:     account_data.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: ALL
Bind array:     1 rows, maximum of 8388608 bytes
Continuation:    none specified
Path used:      Conventional

Table ACCOUNT, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *   ,  O(") CHARACTER
FIELD3                               NEXT     *   ,  O(") CHARACTER
FIELD4                               NEXT     *   ,  O(") CHARACTER
FIELD2                               NEXT     *   ,  O(") CHARACTER
FIELD1                               NEXT     *   ,  O(") CHARACTER
FIELD5                               NEXT     *   ,  O(") CHARACTER
FIELD6                               NEXT     *   ,  O(") DATE yyyy-mm-dd
FIELD7                               NEXT     *   ,  O(") CHARACTER
FIELD8                               NEXT     *   ,  O(") CHARACTER


Table ACCOUNT:
  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:                   2313 bytes(1 rows)
Read   buffer bytes: 8388608

Total logical records skipped:          0
Total logical records read:             4
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue Feb 22 22:15:41 2011
Run ended on Tue Feb 22 22:15:41 2011

Elapsed time was:     00:00:00.32
CPU time was:         00:00:00.05
~
~

阅读(378) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~