Shulou(Shulou.com)06/01 Report--

How to understand SQL*Loader tools, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The SQL*Loader tool is a very powerful external data loading tool for Oracle. The editor will briefly discuss the structure, configuration, and outstanding features of the SQL*Loader tool.

Here is a diagram of the structure of the SQL*Loader tool:

The above figure describes the physical files used by the SQL*Loader work, including the input text file and the output database; the work of the SQL*Loader tool requires at least one control file, which contains the descriptive information of data loading, and SQL*Loader loads, converts and writes the data to the database according to the description in the control file. In addition to the descriptive information of data loading, the control file can also contain parameter information and loaded data. Parameter information includes direct path loading parameters, etc. You can specify a separate configuration file through the parfile parameter of the sqlldr tool. It should be noted here that the parameters specified on the sqlldr command line will override the parameters specified in the control file. Individual data files are specified through the INFILE attribute in the control file.

After the SQL*Loader tool runs, it will produce three files, one is a log file, which records the loading of the data and the loading results in detail, and specifies that two additional files, Bad FIles and Discard Files,Bad Files, save the data that caused the SQL error (that is, the input data is "bad"), and Discard Files saves the data abandoned by the loading process. After loading, the verification of these files can understand the loading situation and judge whether the loading is successful or not.

SQL*Loader supports traditional path loading and direct path loading. Traditional path loading SQL*Loader tools will convert data into SQL statements to execute, resulting in corresponding undo and redo data. Direct path loading is a direct operation of data blocks, and will not produce corresponding undo and redo data, which is especially suitable for loading work with large amounts of data.

From the above discussion, we can understand that the core of the SQL*Loader tool is the control file, and the core configuration of the control file is the definition of loading data. Here is a simple example to discuss the configuration in the control file:

/ * /

Load DATA

# means to load data.

INFILE'C:\ sqlldr\ olap.date_time_15_min.txt'

# INFILE specifies the data file. If it is INFILE *, the loaded data is saved in the control file.

INTO TABLE test.date_time_15_min

# the specific table name to which the data is loaded.

APPEND | REPLACE | TRUNCATE

# if there is no such line, the default is INSERT operation, which requires that the table must be empty. TRUNCATE operation is not TRUNCATE TABLE DATE_TIME_15_MIN in the traditional sense, but TRUNCATE TABLE DATE_TIME_15_MIN REUSE STORAGE operation, which needs to be noted.

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

The storage of # data is divided by symbols, "symbols are cited to represent strings, which is delimited loading, which is the most traditional way, and it can also be loaded with fixed length, which we will not discuss here.

TRAILING NULLCOLS

# if the data entered does not exist for a column, SQLLOADER binds a null value for that column.

(APPLIEDTIME "to_date (: appliedtime,'yyyy-mm-dd hh34:mi:ss')"

# starts with (symbol to represent the field of the table. Because it is a date field, you need to use a function for conversion. The function must be enclosed in double quotation marks.: appliedtime corresponds to the previous APPLIEDTIME.

Applieddate "to_date (: applieddate,'yyyy-mm-dd hh34:mi:ss')"

Psvcode

# if the field does not specify a storage type, the default is CHAR (1000). If the length exceeds the length, you will receive an error from Fileld in data file exceeds maximum length. You only need to specify the type according to the length, for example, CHAR (1000).

Hzpsvcode

Pointindex

Appliedhour "to_date (: appliedhour,'yyyy-mm-dd hh34:mi:ss')"

)

BEGINDATA

# the following is the loaded data, corresponding to the INFILE * settings above.

.

/ * /

In addition to defining how the data is loaded, the control file can also save SQLLOADER parameters and loaded data.

In addition, starting from Oracle 9i, Oracle has introduced the function of external tables. External tables are another way to load data quickly, following the parameters of SQL*Loader to replace SQL*Loader tools. It has better flexibility than SQL*Loader tools, it can be more flexible to deal with and filter data (SQL statements), schedule data loading, and learn relatively less knowledge.

Starting from Oracle Database 10g, Oracle launched the external table unloading function ORACLE_DATAPUMP can easily unload data to flat files, which can be quickly loaded to other databases through the transfer of flat files. Oracle does not provide special tools to unload data from the database to flat files, which can be unloaded by SQL statements, external table unloading function, PL/SQL, and so on.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.