Skip to Main Content
  • Questions
  • csv output using sqlplus spool is very slower than expdp

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Siyavus.

Asked: May 09, 2024 - 1:04 pm UTC

Last updated: May 13, 2024 - 1:29 am UTC

Version: Oracle Databse Ent. 11.2.0.4 G

Viewed 100+ times

You Asked

Dear friends,

I try to export csv from a patitoned table ( 300GB ) it takes 3 hours (only one table), using following code
set term off
set feed off
set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set trimspool on
SET TERMOUT OFF
spool '/backup/csv/Mytable.csv'

SELECT /*+ parallel */ /*csv*/   col1  || '|' ||  col2  || '|' ||   col3   
         FROM MySchema.MyTable ;

spool off
exit;



but when I export (expdp ) all schema tables & its data (3TB) it takes only 20 minutes!

why expdp is very fast comparing to sql spool ?
what is fast method csv output from oracle table ?
regards
Siya AK

Hardware 4 TB Ram + 196 core cpu + nvme disk
oracle 11g r2

and Connor said...

expdp is reading blocks from the database and dumping them out to file, and can be done in parallel.

sqlplus is reading rows from the database, formatting them, then concatenating a CSV string and then writing that to file.

The "parallel" on your sqlplus query lets the database get the data in parallel, but sqlplus still is a serial process that is spooling out the CSV.

If you're looking for unload options, here's a video that covers some alternatives


More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.