[Oracle] SQL * Loader detailed tutorial

Enter sqlldr. If no parameters are followed, a simple description of all command line parameters and their default values are displayed. (You can also query some parameters quickly when you forget them ):

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)

To avoid entering the same parameter in the command line every time, you can also use the parameter file or write the parameter in the OPTIONS clause of the control file. Of course, the command line parameter has the highest priority, it can overwrite the parameter configurations in the parameter file and control file.

SQL * Loader is imported to the local database by default, but it also supports importing to the remote database. You only need to add "@ remote database connection string" after username, as shown below:

> Sqlldr CONTROL = ulcase1.ctl
Username: scott @ inst1
Password: password The following is a detailed explanation of the command line parameters:

BAD-bad file name (. BAD)

CONTROL-CONTROL file name (. ctl)

DATA-DATA file name (. dat) is equivalent to the INFILE in the control file. Multiple DATA files can be specified.

DIRECT-true indicates DIRECT path loading, and false indicates loading using traditional paths.

DISCARD-lost file name (. dsc)

DISCARDMAC-the maximum number of records allowed to be lost. If this number is exceeded, loading stops.

ERRORS-insert failure on the maximum number of records to be previewed. If this number is exceeded, the loading will be stopped.

EXTERNAL_TABLE-whether to use the External table Loading Method

FILE-this parameter is only useful for parallel loading of direct paths.

LOAD-Maximum number of records allowed to be loaded

LOG-log file name (. LOG)

PARALLEL-true or false: whether to use PARALLEL mode for direct path Loading

PARFILE-parameter file name (. par)

ROWS-specify the number of records to be submitted

SKIP-specify how many records are ignored

SKIP_INDEX_MAINTENANCE-true indicates that indexes are not maintained during direct path loading, and the index status changes to unusable.

Next, let's take a look at the highlights of page 2nd:

1 2 Next Page

[Content navigation]
Page 7: command line parameters	Page 1: Control File