viernes, 16 de enero de 2009

How to put dumps in multiple locations

The %U causes file name to be generated by oracle and sequentially like rest01.dmp, rest02.dmp and etc.

Below is an example which will take dump of tablespace users and each dump size will be 300K and it will span in C:\, D:\ and E:\ drive each.

SQL> create or replace directory d as 'd:';
Directory created.

SQL> create or replace directory e as 'e:';
Directory created.

SQL> create directory c as 'c:';
Directory created.

$expdp maximsg/a dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=users

Connected to: Oracle Database 10g Enterprise Edition Release - ProductionWith the Partitioning, OLAP and Data Mining optionsStarting "MAXIMSG"."SYS_EXPORT_TABLESPACE_01": maximsg/******** dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp filesize=300K tablespaces=usersEstimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 20.12 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "CR_2"."O_CDR_TEST" 310.9 KB 2166 rows. . exported "CR_2"."O_CDR" 484.4 KB 2606 rows. . exported "CR_2"."USER_IP" 126 KB 3403 rows. . exported "CR_2"."O_CDR_TEMP" 33.72 KB 361 rows. . exported "CR_2"."USERS" 42.57 KB 230 rows. . exported "SCOTT"."DEPT" 5.656 KB 4 rows. . exported "SCOTT"."EMP" 7.851 KB 15 rows. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows. . exported "SCOTT"."BONUS" 0 KB 0 rowsMaster table "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

******************************************************************************Dump file set for MAXIMSG.SYS_EXPORT_TABLESPACE_01 is:D:\PART1.DMPE:\PART2.DMPC:\REST01.DMPC:\REST02.DMPC:\REST03.DMPJob "MAXIMSG"."SYS_EXPORT_TABLESPACE_01" successfully completed at 12:16:33For dumpfile=d:part1.dmp,e:part2.dmp,c:rest%U.dmp,first in D: drive part1.dmp will be created with sized 300K.Then in E: drive part2.dmp will be created with sized 300K.Then all remaining parts of the dump will be created in C: drive each with 300K (possibly except last one due to dump size is not multiple of 300K) and their name will be like rest01.dmp, rest02.dmp etc.