Batch import data to Oracle databases

Batch import data to Oracle databases

SQL * LOADER is large data

Repository selection method, because it provides the fastest way (DIRECT, PARALLEL ). Now, let's leave aside its theory and use instances to make

You can quickly learn how to use SQL * LOADER.

First, let's get to know SQL * LOADER.

In NT, the SQL * LOADER command is SQLLDR, and in UNIX it is generally sqlldr/sqlload.

For example, run: 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 values)

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

Load -- Number of logical records to load (all default values)

Errors -- Number of errors to allow (50 by default)

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 (65536 by default)

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 (FALSE by default)

Skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (FALSE by default)

Commit_discontinued -- commit loaded rows when load is discontinued (default value: FALSE)

Readsize -- Size of Read buffer (1048576 by default)

Please note: the command line parameters can be specified by the location or keyword.

The example of the former is 'sqlload scott/tiger foo ';

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

The specified location parameter must be earlier than but cannot be later than the parameter specified by the keyword. For example,

'Sqlload SCott/tiger control = foo logfile = log ',

However, 'the sqlload scott/tiger control = foo log ',

Even if the location of the 'log' parameter is correct.

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 start to explain its usage with instances.

Once the data source result.csv exists, it will be imported to the FANCY user in ORACLE.

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

We can see that the four columns are separated by commas as variable-length strings.

2. Create the control file result. ctl.

Result. ctl content:

Load data

Infile 'result.csv'

Into table resultxt

(Resultid char terminated ',',

Website char terminated ',',

Ipport char terminated ',',

Status char terminated by whitespace)

Note:

Infile index 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 is the APPEND method, or REPLACE

Terminated by ',' is separated by commas

Terminated by whitespace is separated by Spaces

3. Execute loading:

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 RESULTXT table.

ORA-04043: the object RESULTXT does not exist

An error is prompted because the database does not have the corresponding table.

4. Create a table in the database

Create table resultxt

(Resultid varchar2 (500 ),

Website varchar2 (500 ),

Ipport varchar2 (500 ),

Status varchar2 (500 ))

 

/