oracle批量数据导入工具 sqlldr
sqlldr工具参数：
[oracle@server ~]$ sqlldr SQL*Loader: Release 11.2.0.3.0 - Production on Wed Nov 19 09:47:56 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Usage: SQLLDR keyword=value [,keyword=value,...] Valid Keywords: userid -- ORACLE username/password control -- control file name log -- log file name bad -- bad file name data -- data file name discard -- discard file name discardmax -- number of discards to allow (Default all) skip -- number of logical records to skip (Default 0) load -- number of logical records to load (Default all) errors -- number of errors to allow (Default 50) rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- size of conventional path bind array in bytes (Default 256000) silent -- suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- file to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE) commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576) external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED) columnarrayrows -- number of rows for direct path column array (Default 5000) streamsize -- size of direct path stream buffer in bytes (Default 256000) multithreading -- use multithreading in direct path resumable -- enable or disable resumable for current session (Default FALSE) resumable_name -- text string to help identify resumable statement resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200) date_cache -- size (in entries) of date conversion cache (Default 1000) no_index_errors -- abort load on any index errors (Default FALSE) PLEASE NOTE: Command-line parameters may be specified either by position or by keywords. An example of the former case is 'sqlldr scott/tiger foo'; an example of the latter is 'sqlldr control=foo userid=scott/tiger'. One may specify parameters by position before but not after parameters specified by keywords. For example, 'sqlldr scott/tiger control=foo logfile=log' is allowed, but 'sqlldr scott/tiger control=foo log' is not, even though the position of the parameter 'log' is correct. [oracle@server ~]$
创建测试表：
[oracle@server ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 19 09:39:58 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test(name varchar2(20), id varchar2(20)); Table created. SQL> exit
准备要导入数据data.txt：
[oracle@server ~]$ vi data.txt jack,20141119 jimmy,20141118 sara,20141120
编写导入控制文件input.ctl：
[oracle@server ~]$ vi input.ctl LOAD DATA INFILE 'data.txt' INTO TABLE test APPEND FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (name, id)
执行导入：
[oracle@server ~]$ sqlldr userid=\'/ as sysdba\' control=input.ctl SQL*Loader: Release 11.2.0.3.0 - Production on Wed Nov 19 09:44:46 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 3
可以看出影响记录为3行，说明导入成功。
另外，导入过程的详细信息可以在默认日志文件input.log中查看，如：
[oracle@server ~]$ more input.log SQL*Loader: Release 11.2.0.3.0 - Production on Wed Nov 19 09:44:46 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Control File: input.ctl Data File: data.txt Bad File: data.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional Table TEST, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- NAME FIRST * , O(") CHARACTER ID NEXT * , O(") CHARACTER Table TEST: 3 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 33024 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 3 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Wed Nov 19 09:44:46 2014 Run ended on Wed Nov 19 09:44:47 2014 Elapsed time was: 00:00:00.30 CPU time was: 00:00:00.02
