Taking Export Backup to Multiple Directories in Oracle

Note: Taking export backup in multiple directories is required if your target data size(table/schema/DB) size is huge i.e. greater than the directory size. In this situation, you can not take the export backup in single directory due to space crunch and client does not want to give additional space. To avoid this space issue, you can take the export backup in multiple directories by estimating the space.

#First create two different directories with two different locations. Ignore this if you have already two different directories.

SQL> create or replace directory DIR_TEST as 'C:\Backup';

Directory created.

SQL> grant read,write on directory DIR_TEST to test;

Grant succeeded.

SQL> create or replace directory DIR_DEMO as 'D:\Backup';

Directory created.

SQL> grant read,write on directory DIR_DEMO to test;

Grant succeeded.

#Check the directory locations and their permissions.

SQL> set lines 300 pages 3000

SQL> col owner for a14

SQL> col directory_name for a25

SQL> col directory_path for a60

SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name like 'DIR%';

OWNER DIRECTORY_NAME DIRECTORY_PATH

------ ---------------- --------------

SYS DIR_DEMO D:\Backup

SYS DIR_TEST C:\Backup

SQL> set lines 300 pages 3000

SQL> col grantee for a14

SQL> col owner for a14

SQL> col table_name for a15

SQL> select grantee,owner,table_name,privilege from dba_tab_privs where grantee='TEST';

GRANTEE OWNER TABLE_NAME PRIVILEGE

-------- ------- --------------- ---------

TEST SYS DIR_TEST READ

TEST SYS DIR_DEMO READ

TEST SYS DIR_TEST WRITE

TEST SYS DIR_DEMO WRITE

#Now execute the below command to take export backup in two different directories. Use parallel keyword to distribute the data across the directories.

C:\Windows\System32>expdp schemas=test dumpfile=DIR_TEST:table_%U.dmp,DIR_DEMO:table_%U.dmp logfile=tables.log parallel=2

Export: Release 19.0.0.0.0 - Production on Sat Mar 9 21:08:56 2024

Version 19.16.0.0.0

Username: test

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "TEST"."SYS_EXPORT_SCHEMA_01": test/******** schemas=test dumpfile=DIR_TEST:table_%U.dmp,DIR_DEMO:table_%U.dmp logfile=tables.log parallel=2

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT