(
ACTNO INTEGER NOT NULL,
ACTKWD VARCHAR2(10 BYTE) NOT NULL,
ACTDESC VARCHAR2(30 BYTE) NOT NULL,
ACTTIME TIMESTAMP(6)
);
INFILE '/home/oracle/rocky/sqlldr/ACT.del' -------导入数据文件路径及名称
TRUNCATE ----操作类型,TRUNCATE清除表中原有记录(操作类型还可以为:INSERT/APPEND/REPLACE)
FIELDS TERMINATED BY ',' ----数据中每列记录用','分隔(还可以用其他符号进行分隔)
OPTIONALLY ENCLOSED BY '"' -------数据中字段用' " '框起
TRAILING NULLCOLS --------表中的字段没有对应值允许插入空值
(ACTNO ,
ACTKWD ,
ACTDESC,
ACTTIME "LOCALTIMESTAMP" -----------插入localtimestamp
)
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)
);
INFILE * ------数据文件在一起,用*表示
TRUNCATE ----操作类型,TRUNCATE清除表中原有记录(操作类型还可以为:INSERT/APPEND/REPLACE)
FIELDS TERMINATED BY ',' ----数据中每列记录用','分隔(还可以用其他符号进行分隔)
OPTIONALLY ENCLOSED BY '"' -------数据中字段用' " '框起
TRAILING NULLCOLS --------表中的字段没有对应值允许插入空值
(ACTNO ,
ACTKWD ,
ACTDESC,
ACTTIME "LOCALTIMESTAMP" -----------插入localtimestamp
)
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]$
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14285854/viewspace-683245/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14285854/viewspace-683245/