本文共 3793 字,大约阅读时间需要 12 分钟。
今天需要导一些数据,从excel导入到数据库中。
没有装现成的plsqldev,只能用sql*loader来弄了。 首先我把excel文件的内容转换成csv文件,以逗号分隔,在另存外excel文件的时候有那个选项。 然后我在目标库中创建了如下的表。 create table sql_summary(sql_time varchar2(100),sql_id varchar2(100),cpu_time varchar2(100),disk_time varchar2(100),exec_time varchar2(100),elapsed_s number);数据类似下面的格式: 140320_165505,gk9u1b5j5702c,1.18E+10,111636718,691,25.14 140320_205539,gk9u1b5j5702c,1.18E+10,110500905,682,25.95 140321_005607,gk9u1b5j5702c,3049428380,29109514,181,22.07 140321_045625,gk9u1b5j5702c,1.21E+10,114246906,705,22.96 140321_085641,gk9u1b5j5702c,1.16E+10,111346877,687,22.9 140319_045158,gpjv97kkg4fv9,50516321,4705112,1,163.46 140313_123928,gqfnh6bf8h2rc,26741932,349150,4,20.4 140317_004719,gs5bhxa1gamww,500924,143350,1,9.09 140316_084548,gt03f296r4cys,11761212,309219,1,33.29 140314_204223,gun4phkc6tkza,23715395,0,1,23.72 140318_125028,gun4phkc6tkza,22455586,0,1,22.47 140318_165049,gun4phkc6tkza,23880370,1,1,23.89 140319_125232,gun4phkc6tkza,23246466,0,1,23.25 140314_124138,gusarx703b7um,50467329,356132,2,56.08 140314_204223,gv6gaza6da96k,69001509,2860,1,73.75 140316_124606,gv6gaza6da96k,69980362,2834,1,74.82 140320_205539,gwpudzhp5zazc,2907558,629750,1,71.79 创建控制文件如下 LOAD DATA INFILE a.txt INTO TABLE sql_summary FIELDS TERMINATED BY "," TRAILING NULLCOLS (sql_time,sql_id,cpu_time,disk_time,exec_time,elapsed_s)
然后调用sql*loader来导入了。粗放一点,其他的日志文件就不指定了。
sqlldr control=a.ctl
也没有报错,就提示了一行信息。但是也不算错误,查看表里,没有数据
DUM1102 /oravl01/oracle> sqlldr control='/oravl01/oracle/a.ctl'
SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 21 17:16:58 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 64
对于上面的信息,查了下,可以在控制文件中加入这个选项来可控制commit的频度。最大目前是165. #OPTIONS (ROWS=100) 设置了以后还是不行。 最后百思不得其解,看看文件的内容吧,vi 一看,原来是dos格式的问题
140314_124138,007yxsxdz7p0h,13721913,165027,1,30.93^M
140314_164158,007yxsxdz7p0h,20656859,330086,2,19.7^M 140314_204223,007yxsxdz7p0h,20669858,330198,2,21.43^M 140315_124337,007yxsxdz7p0h,11427263,165103,1,20.41^M 140315_164358,007yxsxdz7p0h,20966812,330229,2,22.38^M 140315_204420,007yxsxdz7p0h,10631384,165132,1,20.66^M用dosux或者dos2unix格式化一把。
重新试一次。数据算是导入了。:) 脚本如下。 LOAD DATA INTO TABLE sql_summary FIELDS TERMINATED BY "," TRAILING NULLCOLS (sql_time,sql_id,cpu_time,disk_time,exec_time,elapsed_s)sqlldr control=a.ctl data=a.data
DUM1102 /oravl01/oracle> sqlldr control=a.ctl data=a.data
SQL*Loader: Release 11.2.0.2.0 - Production on Fri Mar 21 17:36:44 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128Commit point reached - logical record count 192Commit point reached - logical record count 256Commit point reached - logical record count 320Commit point reached - logical record count 384Commit point reached - logical record count 448Commit point reached - logical record count 512Commit point reached - logical record count 576Commit point reached - logical record count 640Commit point reached - logical record count 704Commit point reached - logical record count 768Commit point reached - logical record count 832Commit point reached - logical record count 896Commit point reached - logical record count 922 DUM1102 /oravl01/oracle> sqlplusSQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 21 17:36:51 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select count(*)from sql_summary;
COUNT(*)
---------- 922
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1126988/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1126988/