Oracle SQL Loader Data Loading tool

Sql*loader is an Oracle data-loading tool that is typically used to migrate operating system files to an Oracle database. Sql*loader is a large data

The Load method that the warehouse chooses to use.

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--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

Recognize false)

Skip_index_maintenance-does not maintain indexes, mark affected indexes as Unus

Able (default false)

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

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

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 of the specified 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.

Introduction to control Files

LOAD DATA

INFILE ' T.dat '//files to be imported

INFILE ' tt.date '//import of multiple files

INFILE *//the content to be imported is in the control file the following begindata is the imported content, * and ' t.dat ' cannot exist at the same time

into table table_name//Specify loaded Tables

Badfile ' c:bad.txt '//designated Bad file address

Here are 4 ways of loading tables

APPEND//original table with data added to the back

INSERT//Load Empty table if the original table has data Sqlloader will stop the default value

REPLACE//original table has data the original data will be deleted

The TRUNCATE//specified content and replace will delete the existing data with the TRUNCATE statement

The specified terminated can be at the beginning of the table and also in the Inner field section of the table

FIELDS terminated by ', ' optionally enclosed by ' '

Load this data: 10,LG, "" "LG" "", "LG,LG"

Results in the table: LG "LG" LG,LG

Terminated by X ' 09 '//in hexadecimal format ' 09 '

Terminated by Writespace//loading this data: LG LG

Trailing Nullcols ************* table fields do not have corresponding values to allow null

The following is a table field

(

Col_1, col_2, Col_filler filler//filler keyword The value of this column will not be loaded

such as: Lg,lg,not results LG LG

)

When it is not stated that fields terminated by ', '

// (

Col_1 [Interger external] terminated by ', ',

col_2 [Date "dd-mon-yyy"] terminated by ', ',

Col_3 [char] terminated by ', ' optionally enclosed by ' LG '

// )

When no statement fields terminated by ', ' use location to tell field to load data

// (

Col_1 position (1:2),

Col_2 position (3:10),

Col_3 position (*:16),//The starting position of this field is at the end of the previous field

Col_4 position (1:16),

Col_5 position (3:10) char (8)//type of the specified field

// )

Did you see the faint? Oh, I first also see dizzy, then from my example to start!

Here, my operating system is XP, the database host is UNIX, and the database is Oracle 10g. Now you want to import the text files from my machine datafile.txt to the remote database.

The number of records in the document is now up to tens of millions of.

Datafile.txt text content:

Js1a,192.168.2.254:80:,running

other,192.168.2.254:80:test.com,stopped

Third,192.168.2.254:81:thirdabc.com,running

......

From there, we see 3 columns, separated by commas, as variable-length strings.

First of all, we now build a database of tables

CREATE TABLE MyData

(

Mac_name VARCHAR2 (10),

Mac_ip VARCHAR2 (50),

Status Varchar2 (10)

)