How To Take Expdp Of A Table To Multiple Directories In Oracle

Suppose you wish to take a expdp backup of a big table, but you don’t sufficient space in a single mount point to keep the dump. In this case we take expdp dump to multiple directory.

DEMO:

Create 2 directories:

SQL> create directory DIR1 as '/home/oracle/DIR1';

Directory created.

SQL> create directory DIR2 as '/home/oracle/DIR2';

Directory created.

SQL> grant all on directory DIR1 to public;

Grant succeeded.

SQL> grant all on directory DIR2 to public;

Grant succeeded.

Now take export with parallel option:

 [oracle@localhost ~]$ expdp dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test.log directory=DIR1 parallel=2 tables=raj.test

Export: Release 12.1.0.2.0 - Production on Thu Aug 27 10:56:31 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "RAJ"."SYS_EXPORT_TABLE_01":  raj/********@orcl dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test.log directory=DIR1 parallel=2 tables=raj.test

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 13 MB

. . exported "RAJ"."TEST"                                10.66 MB   93915 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Master table "RAJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:

  /home/oracle/DIR1/test_01.dmp

  /home/oracle/DIR2/test_01.dmp

Job "RAJ"."SYS_EXPORT_TABLE_01" successfully completed at Thu Aug 27 10:57:09 2015 elapsed 0 00:00:31

Now you can see the dump file has been created in multiple directories.

Dump file set for RAJ.SYS_EXPORT_TABLE_01 is:
/home/oracle/DIR1/test_01.dmp
/home/oracle/DIR2/test_01.dmp

If you wish to compress the size , then you can use compression=all in the expdp command.

For import also you can use the similar method.

[oracle@localhost ~]$ impdp dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test1.log directory=DIR1 parallel=2 tables=raj.test table_exists_action=REPLACE

Import: Release 12.1.0.2.0 - Production on Thu Aug 27 11:28:22 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: raj/raj@orcl

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "RAJ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "RAJ"."SYS_IMPORT_TABLE_01":  raj/********@orcl dumpfile=DIR1:test_%U.dmp,DIR2:test_%U.dmp logfile=test1.log directory=DIR1 parallel=2 tables=raj.test table_exists_action=REPLACE

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "RAJ"."TEST"                                10.66 MB   93915 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "RAJ"."SYS_IMPORT_TABLE_01" successfully completed at Thu Aug 27 11:29:05 2015 elapsed 0 00:00:35

FYI- To use compression parameter in expdp , we need additional Advanced compression license,( Which is not included in enterprise edition)