How to efficiently extract and load Tables in Oracle with sqlplus, sqlldr

Today I’m posting another technical hint for those who might want to extract huge dataset and have to cope with limited disk space issues. SQL Loader is a powerful Oracle tool for bulk loading, and so far the fastest on Oracle.

A major issue is surely the disk space that needs to be occupied while temporarly storing the loading data on local disks. This example will help you overcoming this issue by dumping the data direct on zip files and allowing SQL Loader to read directly from zipped files.

1. Prepare SQL select statement: this is the content inside the select.sql script

set pagesize 0

set head off

set feed off

set line 200

select

col1 || '|' ||

col2 || '|' ||

coln

from

SCHEMA.TABLENAME

;

exit;

2. Extract the dataset (for UNIX systems)

user@machine> mkfifo to-zip

user@machine> gzip -9 -c < to-zip > dataset.dat.gz

user@machine> sqlplus -s username/password@service @select.sql > to-zip

3. Create the SQLLDR control file (control.ctl file)

LOAD DATA

APPEND

INTO TABLE SCHEMA.TABLENAME

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

(

COL1

,COL2

,COLN

)

4. Load the dataset

user@machine> mkfifo unzip

user@machine> zcat dataset.dat.gz > unzip

user@machine> sqlldr userid=$username/$password@$service control=control.ctl log=results.log bad=results.bad discard=results.dsc data=unzip direct=y errors=0

And that’s it! please remember that a good behavior will be always to truncate the table first (if you need to replace the data) and force the rebuild of the table related indexes at the end of the process.