SQL*Loader in Oracle 12c

SQL*Loader in Oracle 12c

 

SQL*Loader is a utility provided  by the Oracle to load the data into Oracle databases from various files like ascii files,textfiles,external files and csv files etc..,

SQL*Loader provides two methods for loading data:

1. conventional path load
2. direct path load

CONVENTIONAL PATH LOAD

Conventional path loads (the default) use the SQL command INSERT and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

Oracle looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.

In conventional path  extra overhead is added as SQL commands are generated, passed to Oracle, and  processed.

DIRECT PATH LOAD

Instead of filling a bind array buffer and passing it to Oracle with a SQL INSERT command, the direct path option creates data blocks that are already in Oracle database block format. These database blocks are then written directly to the database.

The direct path does not contend for free buffers in the buffer cache.

Partial blocks are not used, so no reads are needed to find them and fewer writes are performed.

SQL calls are not performed anytime during the load.

Files related to the  sql*loader :

1. Input file (or) Infile
2. Controlfile
3. Bad file
4. Discard file
5. Log file

Input File (or) Infile :

Infile is the  name of data source file like .csv or .txt  which will be given input to  a control file to look for the data.

Control file :

Control file describes the action to be done by the sql loader  and we can use any text editor to writing a controlfile.

Usually this will be followed by .ctl file extension.

Bad File :

Bad file stores the records which got failed  during the loading operation.

Simply it stores the records which are rejected by the Oracle.

Example : Table has primary key, datatype mismatch etc…,

Bad file is a optional file.

Discard file :

Discard file stores the records which are rejected by user during loading operation.

when user specified when or where condition  as a part of control file, As per the condition few records are rejected and stored in discard file.

Logfile :

Logfile has summary of loading operation like

How many lines are successfully loaded.

How many lines are written to discard file. 

How lines are written to bad file.

Data Loading Options :

INSERT :

Specifies that your loading into an empty table. Sql Loader will abort the load if the table  contains data to start with

Insert is the default option.

APPEND :

Append is used to load the data if table is already have the records.

REPLACE :

Replace specifies that, we want to override the data in the table  before loading

Simple replace will delete all the rows from a table before loading.

TRUNCATE :

Truncate is same like as Replace, But SQL*Loader uses TRUNCATE instead of DELETE command.

Valid Keywords in SQL*Loader

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  (Default all)

skip — number of logical records to skip    (Default 0)

load — number of logical records to load    (Default all)

errors — number of errors to allow   (Default 50)

rows — number of rows in conventional path bind array or between direct path data saves(Default: Conventional path 64, Direct path all)

bindsize — size of conventional path bind array in bytes  (Default 256000)

silent — suppress messages during run (header,feedback,errors,discards,partitions)

direct — use direct path  (Default FALSE)

parfile — parameter file: name of file that contains parameter specifications

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 — do 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)

external_table — use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)

streamsize — size of direct path stream buffer in bytes  (Default 256000)

multithreading — use multithreading in direct path

resumable — enable or disable resumable for current session  (Default FALSE)

resumable_name — text string to help identify resumable statement

resumable_timeout — wait time (in seconds) for RESUMABLE  (Default 7200)

date_cache — size (in entries) of date conversion cache  (Default 1000)

Some examples on SQL*Loader :

Example 1 :

In this example,we are loading the data into dept table(empty table) using insert command.

Step 1: Create a table with required columns and datatypes.

Step 2 : Check input datafile and control file( Verified file format .csv or .txt)

Step 3 : Load the data using SQLLDR utility by specifying userid and control values.

Step 4 : Check the dept table

Step 5 : Check the logfile information.

In log file ,we have following information

Files used during loading operation.

No of lines skipped,loaded and errors.

which type of path we used for loading operation.

How many records loaded into table.

How many records are  rejected by Oracle.

How many records are rejected by user using when condition.

In case its conventional path it shows about bind array occupied size.

At what time loading started ,stopped,Elapsed time and CPU time.

Example 2 :

In this example,we are loading the data into dept table (which have existing records) using append command.

Check the existing table.

Check the infile or input file.

Check the control file with specific when clause.

Use Sqlldr utility to load the data into existing table.

Here only one row successfully added into dept table because of when clause.

Example 3 :

In this example,we are loading the data into dept table (which have existing records) using truncate command.

Truncate command internally performs truncate operation and then it will perform insert command to load the data.

Check the control file with specific when clause.

Use Sqlldr utility to load the data into existing table.

Example 4 :

In this example we loading data into dept table using insert command but  instead of specifying infile,the data has been stored in control file and we used positions instead of delimiters.

Create on empty emp table with required columns  and datatypes.

Check the control file which have data in that.

Use Sqlldr utility to load the data.

Totally table have 4 columns , but here we loaded totally 3 records into four columns

Example 4 :

In this example we are loading the data into multiple tables using insert command.

Create two tables with the names emp2 & emp3 

check the control file which have the commands.

Check the infile which have the records.

Use  sqlldr utility to load the tables.

Here three records loaded into emp2 and emp3 tables that to into specific columns.

 

Thanks for referring this article.