sql loader功能笔记

http://www.orafaq.com/wiki/SQL*Loader_FAQ

创建表

DROP TABLE TESTSL PURGE;
CREATE TABLE TESTSL ( ID NUMBER, TNAME VARCHAR2(200), TEST_DATE DATE, CREATED DATE);
ALTER TABLE TESTSL ADD CONSTRAINT TESTSL_PK PRIMARY KEY (ID);

创建shell script
[oracle@dbs1 demo]$ more testsl.sh
sqlldr control=testsl.ctl, log=testsl.log, bad=testsl.bad, data=testsl.dat userid=rman/rman
sqlplus rman/rman @testsl_verify.sql

创建数据文件
[oracle@dbs1 demo]$ more testsl.dat
1,JOHN,2010-01-01
2,PETER,2010-01-01
3,DANDY,2010-01-01

创建控制文件
[oracle@dbs1 demo]$ more testsl.ctl
load data
INFILE 'testsl.dat'
TRUNCATE
into table testsl
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date "yyyy-mm-dd",
  created sysdate
)

[oracle@dbs1 demo]$ more testsl_verify.sql
select to_char(test_date,'yyyy-mm-dd hh24:mi:ss'),to_char(created,'yyyy-mm-dd hh24:mi:ss') from testsl;
exit

测试运行脚本
./testsl.sh

控制文件包含数据
[oracle@dbs1 demo]$ more testsl.ctl
options (skip=1)
load data
INFILE *
TRUNCATE
into table testsl
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate
)
BEGINDATA
1,JOHN,"2010-02-01 23:00:00"
2,PETER,"2010-02-01 10:00:00"
3,DANDY,"2010-02-01 23:00:00"

测试运行脚本
./testsl.sh

控制文件包含多个数据文件
[oracle@dbs1 demo]$ more testsl.ctl
1,JOHN,"2010-02-01 23:00:00"
2,PETER,"2010-02-01 10:00:00"
3,DANDY,"2010-02-01 23:00:00"

[oracle@dbs1 demo]$ more testsl1.ctl
4,JOHN1,"2010-02-01 23:00:00"
5,PETER1,"2010-02-01 10:00:00"
6,DANDY1,"2010-02-01 23:00:00"

[oracle@dbs1 demo]$ more testsl2.ctl
7,JOHN1,"2010-02-01 23:00:00"
8,PETER1,"2010-02-01 10:00:00"
9,DANDY1,"2010-02-01 23:00:00"

[oracle@dbs1 demo]$ more testsl.ctl
options (skip=1)
load data
INFILE 'testsl.dat'
INFILE 'testsl1.dat'
INFILE 'testsl2.dat'
TRUNCATE
into table testsl
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate
)

控制文件包含多个表(需要加上trailing nullcols, 不然的话就数据就进不了第2和3张表)
[oracle@dbs1 demo]$ more testsl.ctl
load data
INFILE 'testsl.dat'
INFILE 'testsl1.dat'
INFILE 'testsl2.dat'
TRUNCATE
into table testsl
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate
)
into table testsl1
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate
)
into table testsl2
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate
)

控制文件包含表(有大对象的表)
alter table testsl add resume clob;
alter table testsl add file_name varchar2(30);

[oracle@dbs1 demo]$ more testsl.ctl
load data
INFILE 'testsl.dat'
TRUNCATE
into table testsl
fields TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( id    integer,
  tname   CHAR(200),
  test_date date(20) "yyyy-mm-dd hh24:mi:ss)",
  created sysdate,
  file_name CHAR(200),
  resume LOBFILE(file_name) TERMINATED BY EOF
)

[oracle@dbs1 demo]$ more testsl2.ctl
7,JOHN1,"2010-02-01 23:00:00",test.loc
8,PETER1,"2010-02-01 10:00:00",test.loc
9,DANDY1,"2010-02-01 23:00:00",test.loc

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27143/viewspace-672670/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27143/viewspace-672670/