Export and Import Oracle Schemas and Tables with Data Pump

In this article we will look at Data Pump, a utility to export and import Oracle database tables, schemas etc. We’ll look at what Data Pump is, how to use expdb and impdb with examples and also some of the most common errors.

What is Data Pump?

Data Pump is an Oracle database tool for importing and exporting data and metadata into/from a set of operating system files called a dump file set.

Where are the export/import files stored?

Data Pump uses a database directory object. Before using Data Pump you need to make sure the directory is defined and it points to an actual filesystem directory.

Here is an example where to create the directory object dir_data_pump for pdb1 database, mapped to the /home/oracle/datapump directory on my Linux machine:

mkdir /home/oracle/datapump

sqlplus system/pdb1 AS sysdba
ALTER SESSION SET CONTAINER=pdb1;
CREATE OR REPLACE DIRECTORY dir_data_pump AS '/home/oracle/datapump';

You will also need to provide permissions for the user to read and/or write to that directory:

GRANT READ, WRITE ON DIRECTORY dir_data_pump TO mydb;

How to export data using Data Pump?

You can export a single table, several tables, a schema, several schemas or even an entire database.
To keep things simple, in this short article we will look at examples to export some tables and a schema.

Export tables using expdb

expdp mydb/mypassword@cdb1 DIRECTORY=dir_data_pump  TABLES=EMPLOYEE, DEPARTMENT  DUMPFILE=myTableBackup.dmp LOGFILE=myLog.log

Export a schema using expdb

expdp mydb/mypassword@cdb1 DIRECTORY=dir_data_pump  SCHEMAS=mydb DUMPFILE=mySchemaBackup.dmp LOGFILE=myLog.log

How to import data using Data Pump?

You can import the Data Pump export file using impdb.

In the examples below we will look at how to import some tables and a schema

Import tables using impdp

impdp mydb/mypassword@cdb1 directory=dir_data_pump TABLES=EMPLOYEE, DEPARTMENT  DUMPFILE=myTableBackup.dmp LOGFILE=myLog.log TABLE_EXISTS_ACTION=SKIP

NOTE: In this case we used SKIP for the TABLE_EXISTS_ACTION parameter to skip the tables that already exist. This parameter allows values: SKIP, APPEND, TRUNCATE, REPLACE.

Import a table with a new name in the same schema

impdp mydb/mypassword@cdb1 directory=dir_data_pump TABLES=EMPLOYEE DUMPFILE=myTableBackup.dmp LOGFILE=myLog.log REMAP_TABLE=mydb.EMPLOYEE:EMPLOYEE2

Import a schema using impdp

impdp mydb/mypassword@cdb1 DIRECTORY=dir_data_pump SCHEMAS=mydb DUMPFILE=mySchemaBackup.dmp LOGFILE=myLog.log TABLE_EXISTS_ACTION=REPLACE

Import a schema into another schema using impdp

impdp mydb/mypassword@cdb1 DIRECTORY=dir_data_pump SCHEMAS=mydb DUMPFILE=mySchemaBackup.dmp LOGFILE=myLog.log REMAP_SCHEMA=mynewdb

Common errors

Here is a list of some of the most common errors that can occur with expdp and impdp

ORA-39087: directory name is invalid

Make sure you created the directory object using the CREATE OR REPLACE DIRECTORY command as explained above.

ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remappings.

Make sure you grant the required privileges to the user. Most probably you need to grant imp_full_database permission.

grant imp_full_database to mydb;

ORA-27038: created file already exists

This error occurs when the file already exists. Use another file for the DUMPFILE parameter that does not already exist.

ORA-39151: Table “<schema>”.”<table>” exists. All dependent metadata and data will be skipped due to table_exists_action of skip

The table already exists. You need to use the TABLE_EXISTS_ACTION parameter which can have one of these values: SKIP, APPEND, TRUNCATE, REPLACE.
Or you can just drop it before importing (not recommended).

UDI-00014: invalid value for parameter, ‘remap_schema’

Make sure the REMAP_SCHEMA parameter’s value has the following format <schema>:<newschema>
For example:

REMAP_SCHEMA=mydb:mynewdb