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.
