在使用sql*load导入几百万条记录,出现部分记录无法录入,最多录入六万多条记录(这可能是oracle的缓存大小),请教各位如何能正确地导入全部数据。但导入少量数据的时候使用以下方法可以完全导入全部数据
方法如下:
(1)创建表
create table TEMPQUERY(QUERYTIME VARCHAR2(20) ,USERID VARCHAR2(30) ,
QUERYWORDS VARCHAR2(50),URLRANK NUMBER,USERCLICKNUM NUMBER,PAGEURL VARCHAR2(300) )
(2)控制文件 result.ctl
load data
infile 'i.txt'
into table TEMPQUERY
(QUERYTIME char terminated by '\t',
USERID char terminated by '\t',
QUERYWORDS char terminated by '\t',
URLRANK char terminated by ' ',
USERCLICKNUM char terminated by '\t',
PAGEURL char terminated by '\n'
)
(3)运行
userid=scott/tiger@querylog control=result.ctl
(4)数据 i.txt00:00:00 2663722260629922 [减肥] 6 1 www.feirou.com/
00:00:00 06423798298885974 [美女图] 3 1 www.mntoo.com/
00:00:00 35788599169705914 [冲破IP封锁] 3 1 topic.csdn.net/t/20040327/12/2892808.html
00:00:00 0251346029932899 [佣兵天下] 10 1 group.yixia.net/61/good.html
00:00:00 4620183548288722 [舒淇限制级写真] 2 1 www.aidejianzheng.com/articleview/2006-2-24/article_view_2421.htm
00:00:00 6011755170709623 [恋人+李瑞镇] 3 1 podcast.googsc.com/Item/20CR7C/
00:00:00 43575829721155873 [山西省+太原+索纳塔+4S店+最低报价] 1 1 sx.cheshi.com.cn/
00:00:00 2884326221571142 [少女初夜图片] 2 1 bfddcsq1.blog.sohu.com/30889984.html
00:00:00 28880592680317946 [黑色星期天] 6 1 blog.sina.com.cn/u/4adb2d1a01000779
00:00:00 9200165124211159 [博客辅导员红领巾] 992 1 bbs.club.sohu.com/rs-zz0035-146-63513-63513-1172548592.html
00:00:00 6942039291528982 [房价] 8 1 business.sohu.com/s2006/xingzhengfangjia/
00:00:00 20276400932482147 [摩托车网址] 13 1 www.qsmf.com/dispbbs.asp?boardID=58&ID=27790&page=1
00:00:00 20837183204871873 [广州手机网] 4 1 prod.it.com.cn/dealerhtm/7680/index.html
00:00:00 09979389485338275 [《鬼王子》] 1 1 www.readnovel.com/novel/15530.html注:当少量数据时可以完全导入,如果数据很多就会出现只导入数据,请各位指教,十分感谢
方法如下:
(1)创建表
create table TEMPQUERY(QUERYTIME VARCHAR2(20) ,USERID VARCHAR2(30) ,
QUERYWORDS VARCHAR2(50),URLRANK NUMBER,USERCLICKNUM NUMBER,PAGEURL VARCHAR2(300) )
(2)控制文件 result.ctl
load data
infile 'i.txt'
into table TEMPQUERY
(QUERYTIME char terminated by '\t',
USERID char terminated by '\t',
QUERYWORDS char terminated by '\t',
URLRANK char terminated by ' ',
USERCLICKNUM char terminated by '\t',
PAGEURL char terminated by '\n'
)
(3)运行
userid=scott/tiger@querylog control=result.ctl
(4)数据 i.txt00:00:00 2663722260629922 [减肥] 6 1 www.feirou.com/
00:00:00 06423798298885974 [美女图] 3 1 www.mntoo.com/
00:00:00 35788599169705914 [冲破IP封锁] 3 1 topic.csdn.net/t/20040327/12/2892808.html
00:00:00 0251346029932899 [佣兵天下] 10 1 group.yixia.net/61/good.html
00:00:00 4620183548288722 [舒淇限制级写真] 2 1 www.aidejianzheng.com/articleview/2006-2-24/article_view_2421.htm
00:00:00 6011755170709623 [恋人+李瑞镇] 3 1 podcast.googsc.com/Item/20CR7C/
00:00:00 43575829721155873 [山西省+太原+索纳塔+4S店+最低报价] 1 1 sx.cheshi.com.cn/
00:00:00 2884326221571142 [少女初夜图片] 2 1 bfddcsq1.blog.sohu.com/30889984.html
00:00:00 28880592680317946 [黑色星期天] 6 1 blog.sina.com.cn/u/4adb2d1a01000779
00:00:00 9200165124211159 [博客辅导员红领巾] 992 1 bbs.club.sohu.com/rs-zz0035-146-63513-63513-1172548592.html
00:00:00 6942039291528982 [房价] 8 1 business.sohu.com/s2006/xingzhengfangjia/
00:00:00 20276400932482147 [摩托车网址] 13 1 www.qsmf.com/dispbbs.asp?boardID=58&ID=27790&page=1
00:00:00 20837183204871873 [广州手机网] 4 1 prod.it.com.cn/dealerhtm/7680/index.html
00:00:00 09979389485338275 [《鬼王子》] 1 1 www.readnovel.com/novel/15530.html注:当少量数据时可以完全导入,如果数据很多就会出现只导入数据,请各位指教,十分感谢
看下报的是什么错误,可能是你字段里面含有'\t'这种字符,影响了数据导入
如果你没设置errors参数,默认也就是50行,一旦导入出错超过了50行,剩下的也就不导入了
在sqlldr后面加入log和bad参数,重新导入看看错误信息和错误数据