Export and Import Oracle Database using data pump expdp/impdp

This tutorial explains how to export and import an Oracle database using Oracle Data Pump, with expdp and impdp commands. The advantages are the speed, the good dump provided (zero importing problems), remapping of tablespaces and schemas and exporting for different (and previous) versions of Oracle DB.


Note: the export has to be done necessary in the source database server, not in remote, as well as import, from the terminal (command prompt).

Summary

1. Export using EXPDP:
2. Create a new schema on the destination Oracle dbms
3. Empty an existing schema, without deleting and recreating it
4. Import using IMPDP

Esport using EXPDP:

To export and import using expdp we need to have a DIRECTORY object created in ORACLE, that is an oracle identifier for pointing to a directory of the filesystem.
To create a directory that points for instance to C:\ execute as SYSTEM user the query:

CREATE OR REPLACE DIRECTORY oracle_export_dir AS 'C:\';

We suppose is this example that USER/PASSWORD@SID for our connection is MYDB/MYDB@orcl.

To do a normal export, from the command prompt (terminal) type:

expdp MYDB/MYDB@orcl schemas=MYDB directory=ORACLE_EXPORT_DIR dumpfile=MYDB.dmp

Create a new schema on the destination Oracle dbms

Suppose we want to create an empty database MYDB (schema, tablespace and datafile).
Replace the name MYDB with your database name.
If schema and tablespace exist you have to delete them:

DROP USER "MYDB" CASCADE;
DROP TABLESPACE "MYDB" INCLUDING CONTENTS AND DATAFILES;

Execute then the following queries as SYSTEM user (or equivalent):

CREATE TABLESPACE "MYDB" DATAFILE 'MYDB.DBF' SIZE 200 M AUTOEXTEND ON NEXT 50 M MAXSIZE UNLIMITED ;
CREATE USER "MYDB" IDENTIFIED BY "MYDB" ACCOUNT UNLOCK DEFAULT TABLESPACE "MYDB" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";
GRANT "CONNECT" TO "MYDB" WITH ADMIN OPTION;
GRANT "DBA" TO "MYDB" WITH ADMIN OPTION;
GRANT "RESOURCE" TO "MYDB" WITH ADMIN OPTION;
ALTER USER "MYDB" DEFAULT ROLE "CONNECT", "DBA", "RESOURCE";

Database is ready and empty.

Empty an existing schema, without deleting and recreating it

Just execute the following pl/sql query, logged as MYDB (Warning: absolutely NOT as SYSTEM! You will erase everything!)

-- DROP ALL USER OBJECTS
-- ----------------------
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
 FOR cur_rec IN (SELECT object_name, object_type 
     FROM   user_objects
     WHERE  object_type IN 
     ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE', 'TRIGGER', 'SYNONYM', 'INDEX', 'PACKAGE BODY', 'DATABASE LINK')
     ) LOOP
BEGIN
  IF cur_rec.object_type = 'TABLE' THEN
 EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
  ELSE
 EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
 DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
END;
 END LOOP;
END;
/

NOTE: Using Navicat Client this script for some strange reason doesn't work correctly, in this case I suggest this script from Sébastien Lesaint:

http://www.javatronic.fr/tips/2014/11/07/script_to_delete_all_objects_in_a_oracle_schema.html

Import using IMPDP

To export for a previous oracle version, i.e. from version 11g to version 10.2.0.1.0 (change number based on your target version), type:

expdp MYDB/MYDB@orcl schemas=MYDB directory=ORACLE_EXPORT_DIR dumpfile=MYDB.dmp version=10.2.0.1.0

To import dump file previously exported, in a target schema, type from command prompt:

impdp MYDB/MYDB@orcl schemas=MYDB directory=ORACLE_EXPORT_DIR dumpfile=MYDB.dmp

This will import a schema from MYDB (source name) to MYDB (target name).
But could happen that schema name or tablespace name used in source db doesn't match target schema and tablespace names, i.e. we want to import in MYNEWDB.
In this case use remap_schema and remap_tablespace options, in this way:

impdp MYDB/MYDB@orcl schemas=MYDB directory=ORACLE_EXPORT_DIR dumpfile=MYDB.dmp remap_schema=MYDB:MYNEWDB remap_tablespace=MYDB:MYNEWDB