SQL Loader
SQL*Loader is the utility to use for high performance data
loads. The data can be loaded from any text file and inserted into the
database.
During processing, SQL*Loader writes messages to the log
file, bad rows to the bad file, and discarded rows to the discard file.
The Control File
The SQL*Loader control file contains information that
describes how the data will be loaded. It contains the table name, column data
types, field delimiters, etc. It simply provides the guts for all SQL*Loader
processing.
SQL*Loader Options
SQL*Loader provides the following options, which can be
specified either on the command line or within a parameter file:
- bad –
A file that is created when at least one record from the input file is
rejected. The rejected data records are placed in this file. A record
could be rejected for many reasons, including a non-unique key or a
required column being null.
- bindsize –
[256000] The size of the bind array in bytes.
- columnarrayrows –
[5000] Specifies the number of rows to allocate for direct path column
arrays.
- control –
The name of the control file. This file specifies the format of the data
to be loaded.
- data – The name of the file
that contains the data to load.
- direct –
[FALSE] Specifies whether or not to use a direct path load or
conventional.
- discard – The name of the file that
contains the discarded rows. Discarded rows are those that fail the WHEN
clause condition when selectively loading records.
- discardmax –
[ALL] The maximum number of discards to allow.
- errors – [50] The number of
errors to allow on the load.
- external_table – [NOT_USED] Determines
whether or not any data will be loaded using external tables. The other
valid options include GENERATE_ONLY and EXECUTE.
- file – Used only with
parallel loads, this parameter specifies the file to allocate extents from.
- load – [ALL] The number of
logical records to load.
- log –
The name of the file used by SQL*Loader to log results.
- multithreading –
The default is TRUE on multiple CPU systems and FALSE on single CPU
systems.
- parfile –
[Y] The name of the file that contains the parameter options for
SQL*Loader.
- parallel –
[FALSE] Specifies a filename that contains index creation statements.
- readsize –
The size of the buffer used by SQL*Loader when reading data from the input
file. This value should match that of bindsize.
- resumable –
[N] Enables and disables resumable space allocation. When “Y”, the
parameters resumable_name and resumable_timeout are
utilized.
- resumable_name –
User defined string that helps identify a resumable statement that has
been suspended. This parameter is ignored unless resumable =
Y.
- resumable_timeout –
[7200 seconds] The time period in which an error must be fixed. This
parameter is ignored unless resumable = Y.
- rows –
[64] The number of rows to load before a commit is issued
(conventional path only). For direct path loads, rows are the number of
rows to read from the data file before saving the data in the datafiles.
- silent –
Suppress errors during data load. A value of ALL will suppress all load
messages. Other options include DISCARDS, ERRORS, FEEDBACK, HEADER, and
PARTITIONS.
- skip –
[0] Allows the skipping of the specified number of logical records.
- skip_unusable_indexes –
[FALSE] Determines whether SQL*Loader skips the building of indexes that
are in an unusable state.
- skip_index_maintenance –
[FALSE] Stops index maintenance for direct path loads only.
- streamsize –
[256000] Specifies the size of direct path streams in bytes.
- userid – The Oracle username and password.
The Control File Example: -
OPTIONS (SKIP =
1,ERRORS = 1000)
LOAD DATA
INFILE '$File'
truncate INTO TABLE
xx_sddc_ar_cust_cv
FIELDS TERMINATED
BY ','
OPTIONALLY ENCLOSED
BY '"'
TRAILING NULLCOLS
(
OPERATING_UNIT,
ORIG_CUSTOMER_REFERENCE,
CUSTOMER_NAME,
COUNTRY,
ALTERNATE_NAME ,
ORIG_ADDRESS_REFERENCE
,
ADDRESS_LINE_1
,
ADDRESS_LINE_2
,
ADDRESS_LINE_3
,
ADDRESS_LINE_4
,
COUNTY
,
CITY
,
STATE
,
ASSESS_LATE_CHARGES
CHAR TERMINATED BY WHITESPACE
)
Steps:-
1.
Firstly, you will put your Control
file & Data File into top Bin directory
2.
For Example, cd $CUSTOM_TOP
3.
Cd bin
4.
Pwd
5.
Copy the whole path and put control
and data file on this path as well
6.
Now make a concurrent program, with
one parameter p_datafile.
7.
Now you will make a staging table, by
using sql loader all data will be copied into staging table from data file.
No comments:
Post a Comment