oracle load 用法,Oracle中SQL*LOADER使用方法总结

Oracle数据库中SQL*LOADER使用方法总结(一)

sqlldr 有两种使用方法：

1.使用一个控制文件和一个数据文件。

2.只有一个控制文件，控制文件中包含数据。

分别对两种方法用例子进行说明：

1.使用一个控制文件和一个数据文件。

a.创建表

CREATE TABLE SCOTT.ACT_1

(

ACTNO    INTEGER                              NOT NULL,

ACTKWD   VARCHAR2(10 BYTE)                    NOT NULL,

ACTDESC  VARCHAR2(30 BYTE)                    NOT NULL,

ACTTIME  TIMESTAMP(6)

);

b.控制文件Act_Load.ctl代码如下：

LOAD DATA

INFILE '/home/oracle/rocky/sqlldr/ACT.del'     -------导入数据文件路径及名称

TRUNCATE  ----操作类型,TRUNCATE清除表中原有记录(操作类型还可以为:INSERT/APPEND/REPLACE)

INTO TABLE ACT  ----插入记录表

FIELDS TERMINATED BY ',' ----数据中每列记录用','分隔(还可以用其他符号进行分隔)

OPTIONALLY ENCLOSED BY '"' -------数据中字段用' " '框起

TRAILING NULLCOLS --------表中的字段没有对应值允许插入空值

(ACTNO ,

ACTKWD ,

ACTDESC,

ACTTIME "LOCALTIMESTAMP"   -----------插入localtimestamp

)

c.数据文件ACT.del内容如下：

10,"MANAGE","MANAGE/ADVISE"

20,"ECOST ","ESTIMATE COST"

30,"DEFINE","DEFINE SPECS"

40,"LEADPR","LEAD PROGRAM/DESIGN"

50,"SPECS ","WRITE SPECS"

60,"LOGIC ","DESCRIBE LOGIC"

70,"CODE  ","CODE PROGRAMS"

80,"TEST  ","TEST PROGRAMS"

90,"ADMQS ","ADM QUERY SYSTEM"

100,"TEACH ","TEACH CLASSES"

110,"COURSE","DEVELOP COURSES"

120,"STAFF ","PERS AND STAFFING"

130,"OPERAT","OPER COMPUTER SYS"

140,"MAINT ","MAINT SOFTWARE SYS"

150,"ADMSYS","ADM OPERATING SYS"

160,"ADMDB ","ADM DATA BASES"

170,"ADMDC ","ADM DATA COMM"

180,"DOC   ","DOCUMENT"

d.在命令行中输入sqlldr导入数据命令如下：

[oracle@NODE1 sqlldr]$ sqlldr scott/tiger control=./Act_load.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jan 1 22:55:08 2011

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

Commit point reached - logical record count 18

2.只有一个控制文件，控制文件中包含数据。

a.创建表

CREATE TABLE SCOTT.ACT_1

(

ACTNO    INTEGER                              NOT NULL,

ACTKWD   VARCHAR2(10 BYTE)                    NOT NULL,

ACTDESC  VARCHAR2(30 BYTE)                    NOT NULL,

ACTTIME  TIMESTAMP(6)

);

b.控制文件Act_Load1.ctl代码如下：

LOAD DATA

INFILE *     ------数据文件在一起,用*表示

TRUNCATE  ----操作类型,TRUNCATE清除表中原有记录(操作类型还可以为:INSERT/APPEND/REPLACE)

INTO TABLE ACT  ----插入记录表

FIELDS TERMINATED BY ',' ----数据中每列记录用','分隔(还可以用其他符号进行分隔)

OPTIONALLY ENCLOSED BY '"' -------数据中字段用' " '框起

TRAILING NULLCOLS --------表中的字段没有对应值允许插入空值

(ACTNO ,

ACTKWD ,

ACTDESC,

ACTTIME "LOCALTIMESTAMP"   -----------插入localtimestamp

)BEGINDATA

10,"MANAGE","MANAGE/ADVISE"

20,"ECOST ","ESTIMATE COST"

30,"DEFINE","DEFINE SPECS"

40,"LEADPR","LEAD PROGRAM/DESIGN"

50,"SPECS ","WRITE SPECS"

60,"LOGIC ","DESCRIBE LOGIC"

70,"CODE  ","CODE PROGRAMS"

80,"TEST  ","TEST PROGRAMS"

90,"ADMQS ","ADM QUERY SYSTEM"

100,"TEACH ","TEACH CLASSES"

110,"COURSE","DEVELOP COURSES"

120,"STAFF ","PERS AND STAFFING"

130,"OPERAT","OPER COMPUTER SYS"

140,"MAINT ","MAINT SOFTWARE SYS"

150,"ADMSYS","ADM OPERATING SYS"

160,"ADMDB ","ADM DATA BASES"

170,"ADMDC ","ADM DATA COMM"

180,"DOC   ","DOCUMENT"

c.在命令行中输入sqlldr导入数据命令如下：

[oracle@NODE1 sqlldr]$ sqlldr scott/tiger control=./Act_load1.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jan 1 23:08:05 2011

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

Commit point reached - logical record count 18

[oracle@NODE1 sqlldr]$