Performing Oracle Database Export and Import Using Data Pump

In this tutorial, you will learn how to import and export Oracle Database schemas with Data Pump utility. Oracle Data Pump introduced with Oracle 10g. Its features include significant structural and functional enhancements over the initial import and export utilities. Data Pump runs as a server process, benefiting users in multiple ways. The following are the steps to perform Oracle Database export and import using Data Pump.

Create a Directory Object in Oracle

Oracle Data Pump requires a directory to store data files and log files. Use the CREATE DIRECTORY command to create a directory object in Oracle which refers to an external directory on the disk. Users who will access the Data Pump files must have the READ and WRITE privileges on the directory. Also, the user who is creating the directory in Oracle should have CREATE ANY DIRECTORY privilege. Below is an example:

CREATE OR REPLACE DIRECTORY dbpump AS 'f:\test\dbpump';

Folder f:\test\dbpump must exists. Now grant privileges to the user who will perform export and import. The following is an example:

GRANT READ, WRITE ON DIRECTORY DBPUMP TO VINISH;

The user VINISH now can use the DBPUMP directory for Data Pump jobs.

Some Important Data Pump Export Options

Oracle provides the utility expdp that serves as the interface to Data Pump. Below I am describing some essential parameters to use with expdp command, for more details on the parameters, you can check the following link Oracle Data Pump.

EXPDP Parameters

Parameter	Description
CONTENT	What is to be exported: DATA_ONLY, METADATA_ONLY, or ALL.
DIRECTORY	Specifies the Oracle Database directory object.
DUMPFILE	Specifies the DUMP file name.
PARFILE	Parameter file to use.
SCHEMAS	Name of the schemas to be exported.

Exporting With Data Pump in Oracle Example

In the following Data Pump export, the user VINISH will export the SCOTT schema's metadata (Procedures, functions, table and view structures) only.

expdp vinish/[email protected] dumpfile=scott.dmp content=metadata_only directory=dbpump schemas=scott

Output

Export: Release 11.2.0.1.0 - Production on Fri Mar 29 14:26:45 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "VINISH"."SYS_EXPORT_SCHEMA_01": vinish/********@orcl dumpfile=scott.dmp content=metadata_only directory=dbpump schemas=scott
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "VINISH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for VINISH.SYS_EXPORT_SCHEMA_01 is:
F:\TEST\DBPUMP\SCOTT.DMP
Job "VINISH"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:27:03

You can perform the above export using the parameter file also. Here is an example:

Create a parameter file to any directory, for example, f:\test\dbpar.par as following:

DIRECTORY=DBPUMP
CONTENT=METADATA_ONLY
DUMPFILE=SCOTT.DMP
SCHEMAS=SCOTT

Save the changes to the file. Now run the expdp utility using this parameter file. Below is an example:

expdp vinish/[email protected] parfile=f:\temp\dbpar.par

Importing With Data Pump in Oracle Example

The impdp utility is used to import data with Data Pump in Oracle. Same as expdp, the directory object also needed to perform import with impdp, you can create a new directory object in Oracle to refer external directory, or you can use the same directory.

Some IMPDP Parameters

[table id=8 /]

Import Example

In the following Data Pump import, we will import the dump file; we exported above.

impdp vinish/[email protected] dumpfile=scott.dmp directory=dbpump schemas=scott

Import with parameter file (f:\test\dbpar.par) example:

DIRECTORY=DBPUMP
CONTENT=DATA_ONLY
DUMPFILE=SCOTT_ALL.DMP
SCHEMAS=SCOTT

impdp vinish/[email protected] parfile=f:\temp\dbpar.par