[Oracle] Sql*loader Detailed Usage Tutorials (1)-Overview

Sql*loader principle

Sql*loader is a tool provided by Oracle for data loading, which is more suitable for the Business analysis type database (Data Warehouse) and can handle flat files in many formats, and bulk data loading is more efficient than traditional data insertion. It is as follows:

Control file (. ctl): A file that controls how data imports behave (most important files)

Parameter file (optional) (Parameter file) (. Par): You can write parameters directly in the control file, or you can write a separate parameter file

Data files: Flat files that place source data (multiple data files can be imported at once)

Bad file (. Poor): Put data that cannot be loaded correctly into an error file (such as data format, data type problem, etc.) when loading data

Discard file (optional) (Discard file) (. DSC): Some data, although the data format, data type is not a problem, but it is filtered by the logical conditions (by the control file when set), will be placed in the lost file

Log file (. log): Record Sql*loader Data loading process

Sql*loader Data Load mode Sql*loader supports 3 kinds of data loading methods, namely:

• Traditional path Loading (Direct=false): equivalent to insert statement
• Direct path Loading (direct=true): Bypass SGA, direct data into high watermark (HWM) above, can set parallel load, performance is higher than traditional path load, but more restrictive
• External table load (less): Create an external table on the data file before you insert the data from the external table into the target table

Data file record format from the Sql*loader point of view, data files are organized in records, divided into the following three record formats (set by the infile parameter of the control file): fixed length record formatThis is the least flexible, but the performance is the best, and its syntax is as follows:

N

Indicates that each record in the data file is set to a length of n bytes. variable length record format

INFILE "n"

The variable length record is divided into two parts: the record length and the record itself, see the following example:

Load datainfile ' example.dat '  "VAR 3" to table Examplefields terminated by ', ' optionally enclosed by ' "' (col1 char (5 ), col2 char (7)) Example.dat:009hello,cd,010world,im,012my,name is,

var 3 means that the record length is 3 bytes, followed by the record itself, in the above example, the first record length is 9 bytes (009), the value is 9 bytes after (HELLO,CD), the second record length is 10 bytes (010), the value is 10 bytes after (World,im, \ n), \ n represents the line break at the end of the first line, and so on. Stream Record FormatThis is the most commonly used and default record format, where the record is separated by a delimiter with the following syntax:

Datafile_name [terminator_string"] 

Separators are divided into two types: 1. ‘ char_string ‘：用单引号或双引号扩起来的字符串 2.X‘hex_string‘：十六进制的字节字符串。The default delimiter is the newline character ' \ n ', which is a record for each behavior. [

[Oracle] Sql*loader Detailed Usage Tutorials (1)-Overview