Oracle SQL*Loader Options

SQL*Loader

SQL*Loader Options

SQL*Loader provides the following options, which can be specified either on the command line or within a parameter file:  

·     bad – A file that is created when at least one record from the input file is rejected.  The rejected data records are placed in this file.  A record could be rejected for many reasons, including a non-unique key or a required column being null. 

·     bindsize –  [256000] The size of the bind array in bytes. 

·     columnarrayrows – [5000] Specifies the number of rows to allocate for direct path column arrays.

·     control – The name of the control file.  This file specifies the format of the data to be loaded. 

·     data – The name of the file that contains the data to load. 

·     direct – [FALSE] Specifies whether or not to use a direct path load or conventional load.  

·     discard – The name of the file that contains the discarded rows.  Discarded rows are those that fail the WHEN clause condition when selectively loading records.

·     discardmax – [ALL] The maximum number of discards to allow.

·     errors – [50] The number of errors to allow on the load. 

·     external_table – [NOT_USED] Determines whether or not any data will be loaded using external tables.  The other valid options include GENERATE_ONLY and EXECUTE.    

·     file – Used only with parallel loads, this parameter specifies the file to allocate extents from.

·     load – [ALL] The number of logical records to load.  

·     log – The name of the file used by SQL*Loader to log results. 

·     multithreading – The default is TRUE on multiple CPU systems and FALSE on single CPU systems. 

·     parfile – [Y] The name of the file that contains the parameter options for SQL*Loader. 

·     parallel – [FALSE] Specifies a filename that contains index creation statements.

·     readsize – The size of the buffer used by SQL*Loader when reading data from the input file.  This value should match that of bindsize. 

·     resumable – [N] Enables and disables resumable space allocation.  When “Y”, the parameters resumable_name and resumable_timeout are utilized. 

·     resumable_name – User defined string that helps identify a resumable statement that has been suspended.  This parameter is ignored unless resumable = Y.

·     resumable_timeout – [7200 seconds] The time period in which an error must be fixed.  This parameter is ignored unless resumable = Y.

·     rows – [64] The number of rows to load before a commit is issued (conventional path only).  For direct path loads, rows are the number of rows to read from the data file before saving the data in the datafiles. 

·     silent – Suppress errors during data load.  A value of ALL will suppress all load messages.  Other options include DISCARDS, ERRORS, feedback, HEADER, and PARTITIONS.

·     skip – [0] Allows the skipping of the specified number of logical records. 

·     skip_unusable_indexes – [FALSE] Determines whether SQL*Loader skips the building of indexes that are in an unusable state.

·     skip_index_maintenance – [FALSE] Stops index maintenance for direct path loads only. 

·     streamsize – [256000] Specifies the size of direct path streams in bytes.  

·     userid – The Oracle username and password.

To check which options are available in any release of SQL*Loader use this command:

sqlldr help=y