Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 11, 2020 - 4:05 pm UTC

Last updated: May 12, 2020 - 8:22 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I am looking for settings in SQL Plus that will allow to extract data from a large table into multiple files instead of single file. I currently use UNIX's split command but it adds time to my overall processing and am hoping the spool could split the files at the file generation time. The file sizes can be determined by number of records. Example: 1 million record in each file.

# set command used
 set headsep off termout off pagesize 0 arraysize 5000 trimspool on feedback off  set lin 2000
 spool spool_file
# SQL to extract from single table
 SELECT  /* parallel(4) */
 ...
 ...
 FROM table;
 spool off


#UNIX split:
split -l 1000000 -a 10 spool_file


and Chris said...

Spool writes to one file at a time. So to spool to separate files, query the first N% of the rows and spool to file 1. Spool the next N% to file 2, etc.

To run this in parallel, you can have a master script that calls the others, running them in the background. e.g.:

file_1.sh &
file_2.sh &
file_3.sh &
#etc


As all the queries will start at different times this might lead to read-consistency issues (if the rows change while the process starts).

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Spool output to a named pipe.

David Harris, May 12, 2020 - 4:57 am UTC

With named pipes, no data is written in the disk when passing data between reader and writer. The kernel internally pipes the data between reader and writer processes.

For example...

#!/bin/bash

### 1: Create a named pipe.
mkfifo split.pipe

### 2: Start reading from the pipe using your split command. 
split split.pipe -d -a 4 -l 1000 FILE_PART. &

### 3: Run your script and spool to the named pipe.

sqlplus / as sysdba <<HERE
set lines 1000
set pages 0
set trimspool on

spool split.pipe

select rownum from dba_objects;

spool off
HERE

### 4: Remove the pipe
rm split.pipe

Connor McDonald
May 12, 2020 - 8:22 am UTC

Good input, but also platform specific
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.