Exporting Data in Parallel
I recently had a customer that needed to export data from a huge table to a different machine in csv format very fast. The Database was Standard Edition 2 (SE2) so there was no Parallel Query Option to use.
The solution
Since there was no Parallel Query Option available the solution was to split the huge table logically into several parts (with the analytic function ntile) and using multiple SQLcl processes on the client machine to export these logical parts. While exporting the data we use 7z to compress the data on the fly before writing the final compressed csv file to disk. In that way we were able to reduce the runtime of the export from 20 hours to 16 minutes.
The files
run_parallel.bat
This is the main file to start the export. It will spawn a configurable number of workers. If you want to change the number of workers the variable NUM_WORKERS in this file and the variable NUM_WORKERS in the file worker.sql needs to be adjusted.
@echo off
setlocal enabledelayedexpansion
echo ============================================================
echo Parallel Batch Runner - Starting Workers
echo ============================================================
echo.
REM Set number of Workers
set NUM_WORKERS=5
REM Get database password
REM Clean up any old marker and zip files
del /Q worker_*_done.txt 2>nul
del /Q CSV_*.7z 2>nul
REM Record start time
echo Start Time: %TIME%
echo %TIME% > start_time.tmp
echo.
REM Launch workers
echo Launching %NUM_WORKERS% workers...
for /L %%i in (1,1,%NUM_WORKERS%) do (
start "Worker %%i" /MIN cmd /c worker.bat %%i
echo Worker %%i launched
)
echo.
echo All workers launched.
echo Waiting for completion...
echo.
REM Wait for all workers
:WAIT_LOOP
set /A COUNT=0
for /L %%i in (1,1,%NUM_WORKERS%) do (
if exist worker_%%i_done.txt set /A COUNT+=1
)
echo Workers completed: !COUNT!/%NUM_WORKERS%
timeout /t 5 /nobreak >nul
if !COUNT! LSS %NUM_WORKERS% goto WAIT_LOOP
REM Record end time
echo %TIME% > end_time.tmp
echo.
echo ============================================================
echo All workers completed!
echo ============================================================
echo.
REM Read times from files
set /p START_TIME=<start_time.tmp
set /p END_TIME=<end_time.tmp
echo Start Time: %START_TIME%
echo End Time: %END_TIME%
REM Calculate elapsed (call subroutine)
call :calc_elapsed
echo.
REM Clean up
del /Q worker_*_done.txt 2>nul
del /Q start_time.tmp end_time.tmp 2>nul
echo Press any key to exit...
pause >nul
goto :EOF
:calc_elapsed
REM Extract hours, minutes, seconds from START_TIME
set START_TIME_CLEAN=%START_TIME: =0%
set START_TIME_CLEAN=%START_TIME_CLEAN:,=.%
for /f "tokens=1-3 delims=:." %%a in ("%START_TIME_CLEAN%") do (
set HH1=%%a
set MM1=%%b
set SS1=%%c
)
REM Extract hours, minutes, seconds from END_TIME
set END_TIME_CLEAN=%END_TIME: =0%
set END_TIME_CLEAN=%END_TIME_CLEAN:,=.%
for /f "tokens=1-3 delims=:." %%a in ("%END_TIME_CLEAN%") do (
set HH2=%%a
set MM2=%%b
set SS2=%%c
)
REM Remove leading zeros and convert to decimal
set /a HH1=1!HH1!-100
set /a MM1=1!MM1!-100
set /a SS1=1!SS1!-100
set /a HH2=1!HH2!-100
set /a MM2=1!MM2!-100
set /a SS2=1!SS2!-100
REM Convert to seconds
set /a START_SECS=HH1*3600+MM1*60+SS1
set /a END_SECS=HH2*3600+MM2*60+SS2
REM Calculate difference
set /a ELAPSED=END_SECS-START_SECS
REM Handle midnight rollover
if %ELAPSED% LSS 0 set /a ELAPSED+=86400
REM Convert back to HH:MM:SS
set /a HH=ELAPSED/3600
set /a MM=(ELAPSED%%3600)/60
set /a SS=ELAPSED%%60
REM Add leading zeros
if %HH% LSS 10 set HH=0%HH%
if %MM% LSS 10 set MM=0%MM%
if %SS% LSS 10 set SS=0%SS%
echo Elapsed Time: %HH%:%MM%:%SS%
echo Total Seconds: %ELAPSED%
goto :EOFworker.bat
These are the individual worker files that will be spawned from run_parallel.bat. This file starts the SQLcl instance and connect to the database to export the csv data. Adjust the paths and connection data as required by your environment.
@echo off
REM Parameter %1 is the worker ID
set WORKER_ID=%1
echo Worker %WORKER_ID%: Starting...
REM SQLcl options
set _JAVA_OPTIONS=-Xms32m -Xmx64m
set JAVA_HOME=C:\Program Files\Java\jdk-17
set TNS_ADMIN=%CD%
REM start SQLcl and 7z:
c:\sw\sqlcl\bin\sql -S user1/changeme@orcl @%CD%\worker.sql "%WORKER_ID%" | "C:\Program Files\7-Zip\7z.exe" a -siCSV_%WORKER_ID%.csv -aoa -mmemuse=128m -mmt=off -mx2 %CD%\CSV_%WORKER_ID%.7z
echo Worker %WORKER_ID%: Completed!
REM Create completion marker file
echo Done > worker_%WORKER_ID%_done.txt
exit /b 0worker.sql
This file contains the SQL command each worker starts and is used to split the big table into logical parts.
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
--SET TERMOUT OFF
SET HEADING OFF
SET LINESIZE 32767
SET PAGESIZE 0
SET TRIMSPOOL ON
SET TRIMOUT ON
SET COLSEP ';'
SET DEFINE ON
SET ARRAYSIZE 5000
SET TIMING OFF
SET TERMOUT OFF
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
SET TERMOUT ON
DEFINE WORKER_ID = &1
DEFINE NUM_WORKERS = 5
-- Header row
PROMPT OWNER;OBJECT_NAME;SUBOBJECT_NAME;OBJECT_ID;DATA_OBJECT_ID;OBJECT_TYPE;CREATED;LAST_DDL_TIME;TIMESTAMP;STATUS;TEMPORARY;GENERATED;SECONDARY;NAMESPACE;EDITION_NAME;SHARING;EDITIONABLE;ORACLE_MAINTAINED
-- Data query
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED
FROM
(
SELECT
t.*,
NTILE(&&NUM_WORKERS) OVER (ORDER BY t.object_id) AS worker_id
FROM
t
)
WHERE worker_id = &&WORKER_ID;
--SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON
SET HEADING ON
exittnsnames.ora
This is just a regular tnsnames.ora file used to connect to the database. We increased the SDU size for the export to have fewer round trips over the network.
orcl =
(DESCRIPTION =
(SDU=32767) # don't forget to adjust the sqlnet.ora on the database server for this option to have an effect
(ADDRESS = (PROTOCOL = TCP)(HOST = win4.fritz.box)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.fritz.box)
)
)Demo
To show how these scripts are working we create a sample table t with the following command:
create table t tablespace users as select a2.* from all_objects a1, all_objects a2 where rownum<=1e6 order by a1.object_id;Then we start .\run_parallel.bat to start the export and wait until all workers finish. The output will be similar to:
C:\sw\run_parallel>.\run_parallel.bat
============================================================
Parallel Batch Runner - Starting Workers
============================================================
Start Time: 8:49:40,90
Launching 5 workers...
Worker 1 launched
Worker 2 launched
Worker 3 launched
Worker 4 launched
Worker 5 launched
All workers launched.
Waiting for completion...
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 0/5
Workers completed: 5/5
============================================================
All workers completed
============================================================
Start Time: 8:49:40,90
End Time: 8:51:19,18
Elapsed Time: 00:01:39
Total Seconds: 99
Press any key to exit...After completion the following csv files are created:
C:\sw\run_parallel>dir
Volume in drive C is driveC
Volume Serial Number is B48B-A701
Directory of C:\sw\run_parallel
12.03.2026 08:51 <DIR> .
12.03.2026 08:51 <DIR> ..
12.03.2026 08:51 263.179 CSV_1.7z
12.03.2026 08:51 298.679 CSV_2.7z
12.03.2026 08:51 306.203 CSV_3.7z
12.03.2026 08:51 280.295 CSV_4.7z
12.03.2026 08:51 324.246 CSV_5.7z
12.03.2026 08:25 2.866 run_parallel.bat
12.03.2026 08:53 308 tnsnames.ora
12.03.2026 08:49 574 worker.bat
12.03.2026 08:40 1.035 worker.sql
9 File(s) 1.477.385 bytes
2 Dir(s) 735.083.913.216 bytes free
Leave a Reply