- 09 Jul, 2022
- 0 Comments
- 2 Mins Read
To Take Export Of A Table To Multiple Directories In Oracle
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 ~]$