Wednesday, 25 February 2015

ORA-39095: Dump file space has been exhausted

Hi there,

when exporting database objects using DataPump, the objects are managed by DataPump in a .dmp file, which you specify with the dump file parameter in the expdp command:

dumpfile=FILENAME.dmp

Now with the exprt, the specified dump file will be created and will grow continuously as the export goes on. If your file system or any other setting limits the maximum allowed size for a file and the dump file reaches that limit and there are still objects remaining to be exported, you will get following error:

ORA-39095: Dump file space has been exhausted

The solution for that problem is that instead of creating one large dump file, create several smaller dump files and set a file size limit for the dump files:

Set the  maximum file size with the following parameter to your preferred value (in this case 1 GB):

filesize = 1000M

And now specify that if the file size limit is reached by the dump file, create the next dump file:

dumpfile=file_%U.dmp

where %U will by a number that will increment with each extra dump file that is created. In this case you would get dump files as followed:

file_1.dmp
file_2.dmp
file_3.dmp
...
file_99.dmp

Note that %U specification for the dump file can expand up to 99 files. If 99 files (99* 1GB = 99 GB) have been generated before the export has completed, it will again return the ORA-39095 error. So in that case you would have to increase the file size.

No comments:

Post a comment