SQL Loader can put some data stored in text format into the Oracle database, is a very convenient and universal tool for data migration between different databases. Disadvantages are slower, and additional data such as blob is a bit troublesome.
usage:
SQLLDR Keyword = Value [, keyword = value, ...]
Effective keyword:
Userid - Oracle UserName / Password
Control - Control File
Log - Recorded log file
Bad - bad data file
Data - data file
Discard - discarded data file
Discardmax - Allows the maximum value of data (all default)
Skip - Number of Logical Records to Skip (default 0)
Load - Number of Logical Records To Load (all default)
ErrorS - Allowed error record number (default 50)
Rows - Number of Rows in Convenctional Path Bind Array or Between Direct Path Data Saves (Number of records per submit, default: regular path 64, all direct paths)
BINDSIZE - SIZE OF Conventional Path Bind Array In Bytes (Default 256000)
The size of the buffer (byte) of the recorded buffer is submitted 256000)
SILENT - Disable Output Information (Header, Feedback, Errors, Discards, Partitions)
Direct - Import in a straight path (default false)
Parfile - Parameter File: Name of File That Contains Parameter Specifications
Parallel - Parallel Import (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)
Readsize - Size of Read Buffer (Default 1048576)
Use Bindsize, where the smaller will be automatically adjusted to the larger.
SQLLDR first calculates a single record length, multiplying ROWS, such as less than bindsize, does not try to expand ROWS to fill Bindsize; if it is exceeded, it is subject to Bindsize.
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)
Note: There are two ways to specify the command line parameters: through the position or by keyword. Example of the former:
'sqlldr scott / tiger foo'; examples of the latter: 'sqlldr control = foo userid = scott / tiger'; cannot use the keyword specified in front to use a mixed method to be set by the location; such as' SqlLDR Scott / Tiger Control = foo logfile = log 'is allowed, but' SqlLDR Scott / Tiger Control = foo log 'is not allowed. Seeing that all command line parameters are specified in keywords for clear.
Control file:
A script file for a control command usually ends with CTL, as follows:
Load Data
Infile 't.dat' to import file
//
Infile 'tt.date' imports multiple files
//
Infile
* Indicates that the content to be imported is imported in BeGindata below the Control file.
INTO TABLE TABLE_NAME Specifies the loaded table
Badfile 'c: /bad.txt' Optional, specify bad file addresses, default generated .bad files with the original text name in the current directory
************* The following is the way 4 of the table
Append original appearance is added behind
//
INSERT load empty table If the original table has data SQLLoader stops the default value
//
Replace The original table has data, all data will be removed
//
Truncate specifies the content and Replace of Replace to delete existing data with a TRUNCATE statement
************ Specify separator
Fields terminated by ','
Optionally Enclosed by '""
// Terminated by Writespace Split
The field of the TRAILING NULLCOLS table does not have a corresponding value to be empty
************* Below is the field of the table
(
COL_1, COL_2, Col_Filler
Filler // Filler Keywords This column will not be loaded
// such as: LG, LG, Not Results LG LG
)
If you don't declare Fields Terminated By ', you can use the following two ways:
1. Specify separator for each column
(
COL_1 [Interger External] Terminated By ',',
COL_2 [Date "DD-MON-YYY"] TERMINATED BY ',',
Col_3 [char] Terminated by ',' Optionally Enclosed by 'lg'
)
2. Use the location to tell the field loading data
(
COL_1 Position (1: 2), col_2 position (3:10),
COL_3 Position (*: 16), // The beginning of this field at the end position of the previous field
COL_4 Position (1:16),
COL_5 Position (3:10) Char (8) // Type of specified fields
)
BeGindata corresponding to the INFILE * to import, in the control file
10, SQL, What
20, LG, Show
