Importing database dump files with impdp

In this guide, we'll cover how to import a DMP file into an oracle database using impdp:

Creating sample data viz and dashboards is a common request when we talk about 'the art of the possible'.  If we're lucky, we can use the "Sample Sales" data set provided OOTB with OBIEE and OAC. Since the beginning of OBIEE time, Sample Sales has served us well. Fast forward to 2018, we're seeing an increase in demos and proof of concepts with client/customer data, and it's not in excel files anymore.

We're getting data sets which are database extracts, commonly called DUMP files.   It's a single file representation of an entire data set within a schema, including tables, fields, permissions and the actual data. Importing dump files into an oracle database can be done via datapump the command line 'impdp'.

Here's how to import a dmp file to an Oracle 12c database. We'll be using command-line sqlplus and an  oracle 12c database which are both in a virtual machine running Oracle Linux. This can also be done using the Data Pump Import Wizard using SQL Developer.

Step 1. Know your database directories 

I found this to be the most common point of failure when importing data via impdp.  Know the dba directories within your oracle db by running. Make sure you're logged in as sysdba:

sqlplus sys as sysdba;
select * from dba_directories;

Step 2. Create and grant applicable privileges

CREATE USER BISAMPLE IDENTIFIED BY Helloworld123;

Creates a schema BISAMPLE with password Helloworld123.

GRANT CONNECT, RESOURCE, DBA TO BISAMPLE;

We are assuming that this user is a power user that can:

1. Allows the user to connect and query the BIDEMO schema (connect)
2. Allows the user to create named types for custom schemas (resource)
3. Allows the user to not only create custom named types and alter and destroy them as well (DBA)

8.  GRANT READ, WRITE ON DIRECTORY DATA TO BIDEMO;

Gives BIDEMO user access to dba_directory 'data', in this example its located at /home/oracle/downloads

Step 3. Import the dump file

    impdp system@orcl full=y directory=DATA dumpfile=BISAMPLE.DMP

Running impdp via command line as outlined above will import the BISAMPLE.dmp file located in /home/oracle/downloads to the schema BISAMPLE.

Step 4. Verify the import

Take a second to log in as the user we created (BISAMPLE) and verify that the quantity of tables we imported are actually in the schema

    sqplus BISAMPLE;
select table_name, status, num_rows from USER_TABLES;

In this import we expect to see 62 tables added to the BISAMPLE schema as verified below

Now we can use the awesome capabilities of Oracle Analytics to connect to this db to perform mash ups, analytics and advanced data viz! I know alot of you guys were asking this as a follow up to our earlier post on creating a shared folder w/in an Oracle Linux Virtual Box.  This post should help. Now you can:

1. Create or receive a DMP file on your host machine, or via another user
2. Transfer the DMP file to the target machine which contains the isolated oracle database
3. Use the OBIEE 12c publically available image to create interactive reports, dashboards, data viz, and more! 

Happy demoing!