PROBLEM:
While running expdp with filesize parameter, got below error.
expdp dumpfile=full1.dmp logfile=full1.log directory=T filesize=100m full=y Starting "SYS"."SYS_EXPORT_FULL_01": /******** AS SYSDBA dumpfile=full1.dmp logfile=full1.log directory=T filesize=1m full=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 251.0 MB . . exported "SYS"."NACL$_HOST_EXP" 6.914 KB 1 rows . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows . . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Job "SYS"."SYS_EXPORT_FULL_01" stopped due to fatal error at Wed Jun 7 15:40:43 2017 elapsed 0 00:04:18
SOLUTION:
FILESIZE parameter limits the size of the dumpfile to the mentioned size. if the dumpsize exceeds that size, it will fail with ORA-39095 error.
So that solution is
- Increase the value of filesize parameter &
- Use wild character in dumpfile parameter as fill1_%U.dmp, so that it can generate multiple dumps
expdp dumpfile=full1_%U.dmp logfile=full1.log directory=T filesize=200M FULL=y
Starting "SYS"."SYS_EXPORT_FULL_02": /******** AS SYSDBA dumpfile=full1_%U.dmp logfile=full1.log directory=T filesize=10M FULL=y Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 251.0 MB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER . . exported "IMADVISOR"."WRI$_IMA_SQL_OBJ_DBTIME" 0 KB 0 rows . . exported "IMADVISOR"."WRI$_IMA_SQL_OBJ_DBTIME_STAG" 0 KB 0 rows . . exported "IMADVISOR"."WRI$_IMA_SQL_TEXT" 0 KB 0 rows . . exported "IMADVISOR"."WRI$_IMA_TASK_ACTIONS" 0 KB 0 rows . . exported "IMADVISOR"."WRI$_IMA_WL_SUBSCRIPTIONS" 0 KB 0 rows . . exported "OM_PROP"."MILESTONETASK":"MILESTONETASK_MIN" 0 KB 0 rows . . exported "OM_PROP"."MILESTONETASK1":"MILESTONETASK_MIN" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_FULL_02" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_FULL_02 is: /export/home/oracle/full1_01.dmp /export/home/oracle/full1_02.dmp /export/home/oracle/full1_03.dmp /export/home/oracle/full1_04.dmp /export/home/oracle/full1_05.dmp /export/home/oracle/full1_06.dmp /export/home/oracle/full1_07.dmp /export/home/oracle/full1_08.dmp /export/home/oracle/full1_09.dmp /export/home/oracle/full1_10.dmp /export/home/oracle/full1_11.dmp /export/home/oracle/full1_12.dmp /export/home/oracle/full1_13.dmp /export/home/oracle/full1_14.dmp /export/home/oracle/full1_15.dmp /export/home/oracle/full1_16.dmp /export/home/oracle/full1_17.dmp /export/home/oracle/full1_18.dmp Job "SYS"."SYS_EXPORT_FULL_02" successfully completed at Wed Jun 7 16:45:27 2017 elapsed 0 00:03:42
%U seems to be limited to 99 files.
%U seems to be limited to 99 files. So if %U is already used, try increasing the filesize.
You can get more than 99 files associated with %U by passing multiple filename strings, comma separated, to DUMPFILE parameter
DUMPFILE=”SID_Exp_%U.dmp”,”SID_Exp_1%U.dmp”,”SID_Exp_2%U.dmp”
Would produce 297 files – With incredibly large databases, there is definitely a balancing act between choosing file size and number of files to write.