In order to get an application running for testing purposes on our servers, my team and I needed some configuration data that was stored on the client's production database. After asking them for what we needed, they soon sent us back an Oracle DMP file. Shoving data from the DMP back into the database would be as simple as using Oracle's imp utility...or so I thought.

So, in case you don't know, DMP ("dump") files are binary files generated by Oracle's exp ("export") command. They can contain data from either an entire database or just part of a database. In our case, the file contained data from just a handful of tables. The imp ("import") command does just the opposite. It takes the data from a DMP file and loads it back into the database.

It was our plan to run imp against the DMP, but for whatever reason, we couldn't get imp to work right. After some fruitless searching on the internet for a solution, I decided to start looking for a program that could convert the DMP data into something simpler (like plain text) so I could add the data to the database myself by hand.

I came across a utility called NXTract. This command-line program will take your DMP file and generate a number of tab-delimited text files, one for each table in the DMP. It supports Oracle 5, 6, 7, 8i, and 9i and has a free evaluation copy that will extract up to 5000 rows from each table in the DMP. It worked like a charm, extracting all the data from my DMP file without a hitch.

If you are using the Java EE version of Eclipse (or at least have the right plugins), you can easily use these text files to import data into your database (click here for screenshots). In the Data Source Explorer view, connect to your database and drill down so you can see the list of tables. Right click on the table you want to import data into and select "Data > Load..."

In the dialog window, select the file that NXTract generated for your Input File. Choose "Tab" for your "Column delimiter" and "None" for your "Character string delimiter". Clicking the "Replace existing data" checkbox will delete all rows from your table before it imports the data from your text file. This is good if the data from your DMP file is meant to replace everything in your table, but if you only want to, say, append a few rows, be sure to uncheck this! Also note that, if this is checked and it happens to encounter any errors while inserting the data from the NXTract file, you will not get your deleted data back!

I never did figure out why imp was acting up. But at least now I know how to get around it. Take that, closed-source, proprietary file formats!