LOADING DATA INTO ORACLE THROUGH SQL LOADER UTILITY

STEP-1:

Place the CSV or any delimited file in the Oracle server (Ex: stud_course.csv). For the sake of example, assume this file has 3 columns i.e. ID, Name, and course.

STEP-2:

The table must be created in Oracle already with column names equal to the text file. As we are loading the data into the existing table, the table must be presented.

Sample DDL:

create table scott.stud_course(
id varchar2(20),
name varchar2(50),
course varchar2(50)
);

STEP-3:

Create a control file (i.e. configuration file) with the below content.

load data
infile “stud_course.csv”
insert
into table scott.stud_course
fields terminated by ‘,’ trailing nullcols
(
id,
name,
course
)

Name this file as ‘student_course.ctl’ and save.

STEP-4:

Run below command from the CMD level in windows or linux terminal (if sa is linux)

sqlldr utility is available in the respective installation location, for example, we can see the utility in C:\app\OraUser\product\12.1.0\dbhome_1\BIN folder if Oracle is installed in Windows.

Command:

sqlldr ORACLE_USER/Welcome1234@orcl control=student_course.ctl log=student_course.log direct=true

Explanation:

ORACLE_USER – database user name
Welcome1234 – user’s password
control=student_course.ctl – Control file’s name which we created above.
log=student_course.log – Log file to store the log messages.
direct=true – use direct method instead of the conventional way – This is much faster

STEP-5:

Once we run the above command, below content will be updated in the log file and data will be loaded into the Oracle table.

Column array rows: 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 10510500
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 2479
Total stream buffers loaded by SQL*Loader load thread: 1983

Run began on Thu Jun 16 16:49:35 2019
Run ended on Thu Jun 16 16:50:01 2019

In our test run, 10 million records (~500 MB) loaded in ~ 30 seconds. Above statistics have been taken from our test run.

Limitation:

For reference: https://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm

Comments

comments