Blog

To take Export of a table to Multiple Directories in Oracle

Create 2 directories:

SQL> create directory DIR 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.

Note : make these physical directories on server level too.

E.g

 mkdir -p /home/oracle/DIR1
mkdir -p /home/oracle/DIR2

Now take export with parallel option:

[oracle@test ~]$ expdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir parallel=2 dumpfile=dir:test_%U.dmp, dir2:test_%U.dmp logfile=testtable1.log

Export: Release 19.0.0.0.0 - Production on Sat Jul 9 18:12:34 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir parallel=2 dumpfile=dir:test_%U.dmp, dir2:test_%U.dmp logfile=testtable1.log
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TESTUSER"."ABC"                            5.585 KB       6 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
**************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dir1/test_01.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 9 18:13:13 2022 elapsed 0 00:00:38

[oracle@test ~]$

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

Dump file set for testuser.abc 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@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir parallel=2 dumpfile=dir:test_%U.dmp, dir2:test_%U.dmp logfile=testtable1.log table_exists_action=REPLACE

Import: Release 19.0.0.0.0 - Production on Sat Jul 9 18:30:58 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir parallel=2 dumpfile=dir:test_%U.dmp, dir2:test_%U.dmp logfile=testtable1.log table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."ABC"                            5.585 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 9 18:32:05 2022 elapsed 0 00:01:01

[oracle@test ~]$ 

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.