Category Archives: Administration

You will find here useful scripts for everyday Oracle database administration.

Datapump in parallel with a shell script

I recently came across an issue while exporting a huge partitioned table for a data migration. The export took years without any obvious reason.

After some research I found that the parallelism wasn’t working, I could only see one datapump worker at a time, then I found this note explaining the behaviour (Doc ID 1467662.1) .

A table with BASICFILE lobs cannot be exported in parallel, oracle advise to convert those BASICFILES to SECUREFILES; this was not possible for me.

The second solution was to run multiple export jobs in parallel, each one exporting a fraction of the table rows. In the example, oracle use a query using a range of the primary key to do an export, this was ok but I needed a more automatic method to divide the rows based on the degree of parallelism I wanted to apply, then I found this blog post from Morten Jensen.
Continue reading Datapump in parallel with a shell script