Converting Oracle Data Pump (.dmp) Files to CSV: A Practical Guide

by

Recently, I needed to convert an Oracle Data Pump (.dmp) file to a CSV format. As an Oracle novice, it wasn’t as straightforward as I’d hoped. So, I’m documenting my process, along with common errors I encountered, to hopefully help anyone else facing a similar challenge. Let’s get started!

0x1 Setting up Oracle in Docker

First, I needed an Oracle database instance. Docker made this incredibly easy:

docker run --rm -d -p 1521:1521 -e ORACLE_PASSWORD=admin123 -v /tmp/oracle:/data gvenzl/oracle-free
  • docker run: Starts a new container.
  • --rm: Automatically removes the container when it stops.
  • -d: Runs the container in detached mode (in the background).
  • -p 1521:1521: Maps port 1521 on your host machine to port 1521 inside the container (Oracle’s default port).
  • -e ORACLE_PASSWORD=admin123: Sets the password for the Oracle SYSTEM user.
  • -v /tmp/oracle:/data: Mounts the /tmp/oracle directory on your host to the /data directory inside the container. This is crucial for data persistence and where we’ll store our import files.
  • gvenzl/oracle-free: Specifies the Oracle Docker image to use (a free developer version).

0x2 Accessing the Container Shell

After running the Docker command, you’ll need to get inside the container:

docker exec -it <your_container_name> bash

Replace <your_container_name> with the actual name of your container (you can find this using docker ps). This command opens a bash shell inside the container.

0x3. Importing the Dump File (impdp)

Now that we’re inside the Docker container, let’s import the .dmp file. Before doing so, we need to create a directory inside Oracle to store the .dmp file and logs. Note that your .dmp file should be located within the /tmp/oracle directory on your host machine, which is mapped to /data in the Docker container.

Within the SQL environment, we start by creating a directory object:

CREATE OR REPLACE DIRECTORY dp_dir AS '/data';

Now, we can initiate the import process:

impdp system/admin123 DIRECTORY=dp_dir DUMPFILE=www.dmp LOGFILE=import_www.log
  • impdp: The Oracle Data Pump import utility.
  • system/admin123: The Oracle user and password we are using to connect.
  • DIRECTORY=dp_dir: Specifies the directory object created in the previous step.
  • DUMPFILE=www.dmp: The name of the .dmp file to import.
  • LOGFILE=import_www.log: The name of the log file to be created during the import.

0x4. Handling Common Import Errors

During the import, I encountered some typical errors, and here’s how I resolved them:

a) Tablespace Does Not Exist:

ORA-39083: Object type TABLE:"LOL"."WTF" failed to create with error:
ORA-00959: tablespace 'LOL' does not exist

This indicates that the tablespace defined in the dump file (LOL in this instance) doesn’t exist in our new database. To fix this, you’ll need to create it:

CREATE TABLESPACE LOL DATAFILE '/data/tb01.dbf' SIZE 100M AUTOEXTEND ON;

b) User Does Not Exist:

ORA-39083: Object type TABLE:"LOL"."WTF" failed to create with error:
ORA-01918: user 'LOL' does not exist

In this case, the schema owner (the user associated with objects in the dump) doesn’t exist. We need to create the user and grant them the necessary privileges:

CREATE USER LOL IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT READ, WRITE ON DIRECTORY dp_dir TO LOL;
GRANT ALL PRIVILEGES TO LOL;

Remember to replace password with an actual password

c) User or role xxx does not exist:

You might encounter several errors like this:

ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BLAH' does not exist...

Technically, these errors do not need to be addressed as they do not prevent your data from being imported.

0x5. Using DBeaver to Export the Imported Data to CSV

After the successful import, I used DBeaver, a free database management tool, to examine and export the data:

  • Connection Details:
    • Service Name: FREE (the name of the default service in the Docker image).
    • User: LOL
    • Role: normal
  • Verifying data:
    sql SELECT * FROM LOL.WTF;
  • Export data: DBeaver has an easy-to-use data export feature. Right-click on the table’s data and select “Export Data” to save the information in CSV format.

Conclusion

Importing Oracle .dmp files can be challenging, particularly with database users, roles, and tablespaces to consider. Hope you can convert your dmp to csv table successfully after reading this post.