Oracle Bulk Data Import Tool SQLLDR

Sqlldr Tool Parameters:

[[Email protected] ~]$ Sqlldr SQL*loader:release11.2.0.3.0-Production on Wed Nov +  the: -: About  theCopyright (c)1982, ., Oracle and/or its affiliates. All rights reserved. Usage:sqlldr keyword=value [, keyword=value,...] Valid Keywords:userid--ORACLE username/Password Control-Controlfilename Log--Logfilename Bad--BadfileName Data--Datafilename Discard--Discardfilename Discardmax--Number of discards to allow (Default all) Skip--Number of logical records to skip (Default0) Load--Number of logical records to load (Default all) errors--Number of errors to allow (Default -) Rows--Number of rowsinchconventional path bind array or between direct path data saves (default:conventional path -, Direct path All) bindsize--size of conventional path bind arrayinchBytes (Default256000) Silent--suppress messages during run (header,feedback,errors,discards,partitions) direct--Use Direct path (Default FALSE) Parfile--Parameterfile: Name offileThat contains parameter specifications parallel-- DoParallel Load (Default FALSE)file--fileTo allocate extents from skip_unusable_indexes--disallow/Allow unusable indexes or index partitions (Default FALSE) skip_index_maintenance-- DoNot 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 (Default1048576) external_table--Use external table forload; not_used, Generate_only, EXECUTE (Default not_used) columnarrayrows--Number of rows forDirect path column Array (Default the) Streamsize--size of direct path stream bufferinchBytes (Default256000) Multithreading--Use multithreadinginchDirect Path Resumable--Enable or disable resumable forCurrent session (Default FALSE) Resumable_name--TextstringTo help identify resumable statementresumable_timeout--wait  Time(inchSeconds forResumable (Default7200) Date_cache--Size (inchEntries) ofDateConversion Cache (Default +) No_index_errors--abort load on any index errors (Default FALSE) Note:command-Line parameters is specified either Byposition or by keywords. An example of the former CaseIs'SqlldrScott/tiger Foo'; an example of the latter is'Sqlldr control=Foouserid=scott/tiger'. One may specify parameters by position beforeparameters 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 theposition of the parameter'Log'is correct. [[Email protected]~]$

To create a test table:

[Email protected] ~]$ Sqlplus/As Sysdbasql*plus:release11.2.0.3.0Production on Wed Nov +  the: the: -  theCopyright (c)1982, ., Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release11.2.0.3.0-64bit Productionwith The partitioning, OLAP, Data Mining and Real application testing Optionssql> CREATE table Test (name VARCHAR2 ( -),IDVARCHAR2 ( -)); Table created. SQL> Exit

Prepare to import data data.txt:

VI Data.txtjack, 20141119 Jimmy, 20141118 Sara, 20141120

Write the import control file Input.ctl:

VI'data.txt', ' "  'ID')

To perform an import:

[Email protected] ~]$ sqlldr userid=\'/As Sysdba\ ' Control=input.ctlSQL11.2. 0.3. 0  + thefollowing:1982, Oracle and/or Its affiliates.   3

You can see that the impact record is 3 lines, indicating that the import was successful.

In addition, the details of the import process can be viewed in the default log file Input.log, such as:

[Email protected] ~]$ MoreInput.log SQL*loader:release11.2.0.3.0-Production on Wed Nov +  the: -: $  theCopyright (c)1982, ., Oracle and/or its affiliates. All rights reserved. Control File:input.ctlData File:data.txt Bad File:data.bad Discard file:none specified (Allow all Disca RDS) number to Load:allnumber to skip:0Errors allowed: -Bind Array: -Rows, maximum of256000bytescontinuation:none specifiedpath used:conventionaltable TEST, loaded from every logical record. Insert optioninchEffect forThis table:append Column Name Position Len term encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------NAME First*, O (") CHARACTERID NEXT *, O (") CHARACTERTable TEST:3Rows successfully loaded. 0Rows not loaded due to data errors. 0Rows not loaded because all when clauses were failed. 0Rows not loaded because all fields wereNULL. Space Allocated forBind array:33024bytes -rows) Read Buffer Bytes:1048576total Logical records skipped:0total Logical Records read:3total Logical records rejected:0total Logical records discarded:0Run began on Wed Nov +  the: -: $  theRun ended on Wed Nov +  the: -: -  theElapsed TimeWas:xx:xx:00.30CPU TimeWas:xx:xx:00.02

Oracle Bulk Data Import Tool SQLLDR