Exhausted by Data pump ORA-39095: Dump file space has been exhausted

Have you run into this issue before and thought the disk was the cause?

Checking the space free and used reveals that there is adequate space.

Did you set the parameter filesize in your parameter file?

If you did, please check the number of dump files created.

ls -l *.dmp |wc -l

Is the number returned 99?

If so, you have hit the limit of the maximum number of files data pump can allocate for an export.

There are two possible solutions:

1. Increase the filesize parameter ensuring you have calculated the space needed for the export, so that it can fit into 99 files
2. Remove the filesize parameter and let Oracle decide how big to make the files. This is dependent on the OS limits and the number of threads or workers specified.

Enabling client side tracing for ODP.Net

Recently I had to assist in troubleshooting a connection issue from an application middle tier to an Oracle database. The issue I faced immediately was how to turn on tracing for the .Net connection and the sqlnet.ora connection, so that both levels of tracing could be captured for investigation.

To enable tracing at both levels, you need to modify the registry values TraceLevel and TraceOption. These are set to 0 or disabled by default. This is a good thing otherwise we would fill up our hard drive rather quickly. The registry key is typically located under the HKEY_LOCAL_MACHINE, SOFTWARE, oracle, ODP.NET, then the specific version 2.x or 4.x that your client uses.

Change the values to

The SQL Net tracing is straight forward with the exception of changing where the trace file will be stored as this changed during the 11g release. In 11g, Oracle changed the default behavior of this with the ADR (Automatic Diagnostics Repository). To turn off the option DIAG_ADR_ENABLED to place the trace file in a location of your choosing. Modify your sqlnet.ora file located in your ORACLE_HOME/network/admin folder, open notepad using the "Run as administrator" option and add the values.

The values documented for the trace level client are 0 for no trace, 4 for user trace, 10 for administration trace, and 16 for Oracle support services trace. This last option provides the most verbose information. Be careful to monitor the file sizes they will grow rapidly on a busy system and fill up your drive.

Be sure to turn these setting back to their original values when you have completed your troubleshooting, otherwise your drive will fill up with trace files.