Basic concepts of SQL loader in Oracle

1, the concept of SQL Loader

Import external data (such as text) data into Oracle database. (For data import, different types of database data migrations)

2, Sqlloader Import data principle: In paragraph (segment table) Insert record

1) Normal: Insert the record into the block below the HWM (high watermark) of the segment to first visit the bitmap to determine if the blocks have free spaces

2 Direct: Insert records into unused blocks above the segment HWM (high watermark), speeding up insertion speed

3. SQL Loader Help

C:\Documents and Settings\liqx>sqlldr "to be equipped with Oracle."

Sql*loader:release 10.2.0.1.0-production on Thursday August 11 13:57:10 2011

Copyright (c) 1982, +, Oracle. All rights reserved.

Usage: Sqlldr keyword=value [, Keyword=value,...]

Valid keywords:

UserID--ORACLE username/password

Control--Controlling file name

Log--Journal file name

Bad--Wrong file name

Data--File name

Discard--Obsolete file names

Discardmax-the number of files that are allowed to be discarded (all defaults)

Skip-The number of logical records to skip (default 0)

Load--The number of logical records to load (all defaults)

Errors-Number of errors allowed (default 50)

Rows--The number of rows in a regular path-bound array or direct path to save data (default: General path 64, all direct paths)

Bindsize--The size (in bytes) of the general Path binding array (default 256000)

Silent--hidden messages during the run (title, feedback, error, scrap, partition)

Direct-Use the immediate path (default FALSE)

Parfile--parameter file: The name of the file that contains the parameter description

Parallel--Perform parallel load default FALSE)

FILE--Files to allocate areas from the following objects

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Skip_unusable_indexes-No unused index or index partitions are allowed/allowed (default FALSE)

Skip_index_maintenance--The index is not maintained and the affected index is marked as useless (default FALSE)

Commit_discontinued--the line that was loaded when the load break was committed (default FALSE)

ReadSize--the size of the read buffer (default 1048576)

External_table--loading with external table; not_used, Generate_only, EXECUTE (default not_used)

Columnarrayrows--Number of rows in the Direct path column array (Default 5000)

Streamsize--The direct path runoff buffer size (in bytes) (Default 256000)

Multithreading-using multithreading in direct paths

Resumable--Enables or disables the current recoverable session (default FALSE)

Resumable_name--A text string that helps identify recoverable statements

Resumable_timeout--Resumable wait time (in seconds) (default 7200)

Date_cache--the size of the date conversion cache (in entries) (default 1000)

Please: Command-Line arguments can be specified by position or keyword.

The former example is ' Sqlloadscott/tiger foo '; An example of the latter situation is ' Sqlldr control=foo userid=scott/tiger '. The position specifies that the parameter must be older than, but not later than, the parameter specified by the keyword.

For example, allow ' Sqlldr scott/tiger control=foo logfile=log ', but do not allow ' Sqlldr scott/tiger control=foo log ', even if the parameter ' log ' is in the correct position.

4, the characteristics of SQL Loader

Oracle has a number of tools that can be used to migrate, backup, and restore data. But each tool has its own characteristics.

For example, exp and IMP can export and export data in the database, which is a good tool for database backup and recovery, so it is mainly used in the hot backup and recovery of the database. Has a fast, easy to use, quick advantages, but also have some drawbacks, such as in the different versions of the database between the export, import process, there will always be such or such a problem, this may be the Oracle Company's own product compatibility problem.

The SQL Loader tool does not have this problem, it can be in the text format of data stored in the smooth import into the Oracle database, is a data migration between different databases is a very convenient and common tool. The disadvantage is slow, in addition to BLOB and other types of data is a bit cumbersome.