Oracle----SQLLDR Usage

Sql*loader is an Oracle data load tool that is typically used to migrate operating system files to an Oracle database. Sql*loader is a large data
The warehouse selection uses the Load method as it provides the quickest way (direct,parallel). Now, let's put aside its theory and use examples to make
You get a quick grasp of how sql*loader is used.
First, let's meet Sql*loader.
Under NT, Sql*loader's command is SQLLDR, which is generally sqlldr/sqlload under UNIX.
As performed: D:\oracle>sqlldr
Sql*loader:release 8.1.6.0.0-production on Tuesday January 8 11:06:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.

Usage: sqlload keyword = value [, Keyword=value,...]
Valid keywords:
UserID-ORACLE Username/password
Control--Control file name
Log--Log file name
Bad-Bad file name
Data--Data file name
Discard--Discard file name
Discardmax--number of discards to allow (all default)
Skip--Number of logical records to skip (default 0)
Load--Number of logical records to load (all default)
Errors--Number of errors to allow (default 50)
Rows-number of rows in conventional path bind array or between direct p
Ath Data saves
(Default: Normal path 64, all direct paths)
Bindsize--Size of conventional path bind array in bytes (default 65536)
Silent--Suppress messages during run (Header,feedback,errors,discards,part
Itions)
Direct-use direct path (default false)
Parfile--Parameter file:name of file that contains parameter specification
S
Parallel--Do parallel load (default false)
FILE--file to allocate extents from
Skip_unusable_indexes--Disallow/allow unusable indexes or index partitions (default
Acknowledge false)
Skip_index_maintenance--Do not maintain indexes, mark affected indexes as Unus
Able (default false)
Commit_discontinued--commit loaded rows when load is discontinued (default false)
ReadSize--Size of Read buffer (default 1048576)
Please note: command line parameters can be specified by location or keyword
。 The former example is ' Sqlload
Scott/tiger foo '; the latter example is ' Sqlload Control=foo
Userid=scott/tiger '. Position specifies that the parameter must be earlier than
But not later than the parameters specified by the keyword. For example
' Sqlload scott/tiger control=foo logfile=log ', but
' Do not allow sqlload scott/tiger control=foo log ', even if allowed
The parameter ' log ' is in the correct position.
D:\oracle>
We can see some basic help information, here, I use the Chinese WIN2000 ADV SERVER.
We know that Sql*loader can only import plain text, so we will now start with an example to illustrate its usage.
First, the existing data source result.csv, to pour into Oracle fancy users.
Result.csv content:
1, default Web site, 192.168.2.254:80:,running
2,other,192.168.2.254:80:test.com,stopped
3,third,192.168.2.254:81:thirdabc.com,running
From here, we see 4 columns, separated by commas, for variable-length strings.
Second, the development of control documents RESULT.CTL
Result.ctl content:
Load data
InFile ' Result.csv '
into table Resultxt
(ResultId char terminated by ', ',
Website Char terminated by ', ',
Ipport char terminated by ', ',
Status char terminated by whitespace)
Description
InFile refers to the data source file Here we omit the default discardfile RESULT.DSC badfile Result.bad
Into table resultxt is insert by default, or into table resultxt append for append, or replace
Terminated by ', ' means separated by commas
Terminated by whitespace ends with white space
Third, we execute the load at this time:
D:\>sqlldr Userid=fancy/testpass Control=result.ctl Log=resulthis.out
Sql*loader:release 8.1.6.0.0-production on Tuesday January 8 10:25:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
sql*loader-941: An error occurred while describing table Resultxt
ORA-04043: Object Resultxt does not exist
An error occurred because the database does not have a corresponding table.
Iv. establishing tables in the database
CREATE TABLE Resultxt
(ResultId varchar2 (500),
Website VARCHAR2 (500),
Ipport VARCHAR2 (500),
Status VARCHAR2 (500))
/
Five, re-execute the load
D:\>sqlldr Userid=fancy/k1i7l6l8 Control=result.ctl Log=resulthis.out
Sql*loader:release 8.1.6.0.0-production on Tuesday January 8 10:31:57 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Reach commit point, logical record Count 2
Reach commit point, logical record Count 3
Has succeeded! We can analyze the process through the log file: The Resulthis.out content is as follows:
Sql*loader:release 8.1.6.0.0-production on Tuesday January 8 10:31:57 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control file: Result.ctl
Data file: Result.csv
Error file: Result.bad
Obsolete file: Not specified
:
(All records can be discarded)
Number of mounts: all
Skip: 0
Allowed Errors: 50
Bound array: 64 rows, maximum 65536 bytes
Continue: Not Specified
Path used: General
Table Resultxt
Loaded from each logical record
Insert option takes effect on this table insert
Column name location length abort wrapper data type
------------------------------ ---------- ----- ---- ---- ---------------------
ResultId First *, CHARACTER
WEBSITE NEXT *, CHARACTER
Ipport NEXT *, CHARACTER
STATUS NEXT * WHT CHARACTER

Table Resultxt:
3-row Onboarding success
0 rows were not loaded due to data errors.
Because all when clauses fail, 0 rows are not loaded.
Because all fields are empty, 0 rows are not loaded.

Space allocated for associative arrays: 65016 bytes (63 rows)
Allocation of memory space except for bound arrays: 0 bytes
Total number of logical records skipped: 0
Total Logical Records READ: 3
Total number of logical records rejected: 0
Total logical records discarded: 0
From Tuesday January 08 10:31:57 2002 Start Running
At Tuesday January 08 10:32:00 2002 Run End
Elapsed time: 00:00:02.70
CPU time is: 00:00:00.10 (available
Vi. Concurrent Operations
Sqlldr userid=/control=result1.ctl direct=true parallel=true
Sqlldr userid=/control=result2.ctl direct=true parallel=true
Sqlldr userid=/control=result2.ctl direct=true parallel=true
When loading large amounts of data (approximately 10GB), it is best to suppress the generation of logs:
Sql>alter TABLE resultxt nologging;
This does not produce redo LOG, which can improve efficiency. The control file then adds a line to the load data: unrecoverable
This option must be applied in conjunction with direct.
In concurrent operations, Oracle claims to be able to handle 100GB of data per hour! In fact, it is estimated to be able to 1-10g even if good, to start the usable structure
The same file, but only a small amount of data, after successful start loading large amounts of data, so as to avoid the waste of time.

http://blog.itpub.net/22969361/viewspace-678489/

Oracle----SQLLDR Usage (RPM)