Data Bulk Import Oracle Database

Sql*loader is a large data

The warehouse chooses the load method to use because it provides the fastest way (direct,parallel). Now, let's put aside the theory and use the example to

You quickly understand how sql*loader is used.

First of all, let's meet Sql*loader.

Under NT, the Sql*loader command is Sqlldr, which is generally sqlldr/sqlload under UNIX.

As implemented: 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 defaults)

Skip--Number of logical records to skip (default 0)

Load--Number of logical records to load (all defaults)

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: General path 64, all direct paths)

Bindsize--Size of conventional path bind array in bytes (default 65536)

Silent--Supdivss 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 false)

Skip_index_maintenance--Does not maintain indexes, mark affected indexes as unusable (default false)

Commit_discontinued--commit loaded rows when load is discontinued (default false)

ReadSize--Size of Read buffer (default 1048576)

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

The former example is ' Sqlload scott/tiger foo ';

The latter example is ' Sqlload control=foo userid=scott/tiger '.

The time at which the position specifies the parameter must be earlier than but not later than the parameter 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 the parameter ' log ' is allowed to be 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 now start with an example to explain its usage.

One, already exists the data source result.csv, wants to pour into Oracle fancy user.

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 this, we see 4 columns, separated by commas, as 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 by default insert, or into table resultxt append as append, or replace

Terminated by ', ' means comma separated

Terminated by whitespace ends with whitespace

Third, we perform loading 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 the table resultxt

ORA-04043: Object Resultxt does not exist

Error prompted 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))

/