[Oracle] common tool set SQL * Loader usage

It is as follows:

Control File: A file used to control the behavior of Data imports (the most important file ).

Parameter file (optional)

Data File: a flat file for storing source data

Error file: when loading data, put the data that cannot be correctly loaded into the error file (such as data format problems, data type problems, etc)

Discarded file (optional): some data, although the data format and data type are correct, is filtered out by the logical conditions (set in the control file) and will be put into the lost file.

Log File: records the data loading process of SQL * Loader.

Direct path Loading
In SQL * Loader, Direct path loading (set Direct = TRUE) is generally used to improve performance ).

Oracle provides two types of insert statements: Regular insert (conventional insert) and direct insert (direct-path insert). direct insert at the cost of some functions, in exchange for efficient loading of a large amount of data.

Direct path loading inserts data directly to the high-water level (HWM) of the segment to be modified, and generates a minimum number of undo (only the undo of the data dictionary is generated, it does not generate undo of data in blocks and does not pass the high-speed cache (SGA). Therefore, it has better performance than regular inserts.

Restrictions on direct path loading include:
1) A table can only be inserted in one direct path at the same time. Therefore, it is not suitable for inserting small data volumes and is only suitable for loading large volumes of data;
2) idle space in HWM will not be used;
3) When a table is inserted in a direct path, the same session cannot perform any operations on it (select is not allowed );
4) ONLY insert inot... select... statements, merge statements, and applications using the OCI direct path interface can be used.