Knowledge Base : DataPumpExportMultiple

33 November 10, 2021

Oracle DataPump can be used to export to multiple files, for example in operating systems like HP-UX, which still have a file size limit.

  • Create directories
SQL> CREATE DIRECTORY EXPORT1 AS '/export/vol1';
Directory created.
SQL> CREATE DIRECTORY EXPORT2 AS '/export/vol2';
Directory created.
  • Create parameter file
$ cat expdp_mtb2p.par
full=y
parallel=1
DUMPFILE=EXPORT1:mtb2p_migration1.dmp,EXPORT2:mtb2p_migration2.dmp
FILESIZE=800G
logfile=mtb2p_migration.log
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYYMMDD HH24MISS'),'YYYYMMDD HH24MISS')"
  • Write shell script wrapper
#!/usr/bin/bash
export ORACLE_HOME=/oracle/dbsystem/product/10.2_3
export ORACLE_SID=mtb2p
/oracle/dbsystem/product/10.2_3/bin/expdp '/ AS SYSDBA' parfile=/oracle/dbsystem/Jan/expdp_mtb2p.par
#EOF
  • execute using at:
echo "/oracle/dbsystem/Jan/expdp_mtb2p.sh" | at 1000

oracle has to be in /usr/lib/cron/at.allow.