expdp: Export Data From Oracle Instantly

Are you looking for an easy way to export your data from Oracle? If yes, then you are in the right place. This blog focuses on exporting a complete database, a schema, a tablespace, and a table from Oracle using the expdp utility. Oracle 10g or a higher version of Oracle uses Data Pump for exporting and importing data. Previous versions of Oracle used ‘exp’ for exporting and ‘imp’ for importing. Oracle prefers exporting data using ‘expdp’ because it is a fast and flexible process compared to ‘exp’ utility. It also supports PL/SQL API.  

So, if you are an Oracle user who wants to export their data effortlessly, then this blog will answer all your queries.

This blog will take you through the following topics:

Table of Contents

• Prerequisites
• Introduction To Oracle
• Introduction To Data Pump
• expdp: Tool Overview
• Steps To Export Data
  • Export A Complete Database
  • Export A Schema
  • Export A Tablespace
  • Export A Table
  • Export A Table Using PAR File
• Conclusion

Prerequisites

• Oracle database 10g or above.
• Knowledge about RDBMS (relational database).
• Need to have access to a privileged user.

Introduction To Oracle

Oracle Corporation introduced the Oracle database as a multi modelled relational database management system. They used to store the data in a single unit, retrieve the data, and manipulate it according to your needs. It provides a logical structure and a physical structure of your data. It was the first database based on enterprise grid computing. The latest Oracle database version is Oracle 11c.

Various features of Oracle database are listed below:

• Oracle offers recovery in case of failure.
• Oracle offers Business Intelligence features such as data warehousing, ETL, etc.
• Oracle offers high security by preventing unauthorized access and auditing user actions.
• Oracle offers portability on all platforms. 

Introduction To Data Pump

Oracle’s Data Pump offers export and import utilities. It is available only on Oracle 10g and later. It is a server-based technology of Oracle. The Data Pump provides high-speed movement of data and metadata.

Data Pump includes two utilities:

• Export utility using ‘expdp’.
• Import utility using ‘impdp’.

Exporting data from the Oracle database to another destination is done using expdp. Importing data from the Oracle database to another destination is done using impdp. All the files which have been exported can only be imported back using impdp. The data which can be exported, can either be a complete database or a subset of the database such as tables, tablespace, etc. Data Pump is used to add another layer of security to your data.

expdp: Tool Overview

Oracle database uses expdp utility to export data from the database. There are various reasons why we export data from one platform to another.

You can also read our article about ‘Oracle Data Pump Export‘.

Let’s look at some of the advantages of expdp:

• The process of exporting data and metadata using expdp is to add another layer of security.
• It also provides a backup of your data. 
• They are 15-50% faster than traditional exports.
• It offers enhanced performance as it is a server-based technology.
• The data that is exported using expdp is always encrypted.

Steps To Export Data

Data can be exported from Oracle database in the following ways:

• Export A Complete Database
• Export A Schema
• Export A Tablespace
• Export A Table
• Export A Table Using PAR File

1. Export A Complete Database

Let’s see what you will cover here:

1. Create A Directory
2. Create A Directory Object
3. Grant Permission To The User
4. Export Database

Step 1: Create A Directory

This step should be done by a privileged user only on the server. For example, I have used this location for my directory:

‘E:OracleExportCompleteExport’

Step 2: Create A Directory Object

A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.

To create a directory object follow these steps:

a. Open command prompt and open sqlplus as ‘sys’ user. 

sqlplus / as sysdba

b. Create an object for the directory by executing the following command.

create directory directory_object_name as 'location’;

Step 3: Grant Permission To The User

Grant permission, such as read and write on the directory to the user who wants to export the data.

grant read,write on directory directory_object_name to user_name;

Grant export privilege to the user using ‘datapump_exp_full_database’.

grant datapump_exp_full_database to user_name;

Step 4:  Export Database

‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt. 

expdp user_name/user_password DIRECTORY=directory_object_name   DUMPFILE=dump_file_name.dmp LOGFILE=log_file_name.log full=y

In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.

2. Export A Schema

Schema can be termed as a collection of the logical structure of data or objects. For example, views.  

Let’s see what you will cover here:

1. Create A Directory
2. Create A Directory Object
3. Grant Permission To The User
4. Export Schema

Step 1: Create A Directory

This step should be done by a privileged user only on the server. 

‘E:OracleExportExport Schema’

Step 2: Create A Directory Object

A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.

To create a directory object follow these steps:

a. Open command prompt and open sqlplus as ‘sys’. 

sqlplus / as sysdba

 b.  Create an object for the directory by executing the following command.

create directory directory_object_name as 'location’;

Step 3: Grant Permission To The User

Grant permission, such as read and write on the directory to the user who wants to export the data.

grant read,write on directory directory_object_name to user_name;

Grant export privilege to the user using ‘datapump_exp_full_database’.

grant datapump_exp_full_database to user_name;

Step 4:  Export Schema

‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt. 

expdp user_name/user_password DIRECTORY=directory_object_name DUMPFILE=dump_file_namel.dmp LOGFILE=log_file_name.log schemas=schema_name 

In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.

3. Export Tablespace 

Tablespace refers to the storage area where the database stores data logically.  So, when you say exporting a tablespace, you mean exporting all the tables in that storage area along with all the dependent objects of that table.

Let’s see what you will cover here:

1. Create A Directory
2. Create A Directory Object
3. Grant Permission To The User
4. Export Tablespace

Step 1: Create A Directory

This step should be done by a privileged user only on the server.   

‘E:OracleExportExport Tablespace’

Step 2: Create A Directory Object

A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.

To create a directory object follow these steps:

a. Open command prompt and open sqlplus as ‘sys’. 

sqlplus / as sysdba

b. Create an object for the directory by executing the following command.

create directory directory_object_name as 'location';

Step 3: Grant Permission To The User

Grant permission, such as read and write on the directory to the user who wants to export the data.

grant read, write on directory directory_object_name to user_name;

Grant export privilege to the user using ‘datapump_exp_full_database’.

grant datapump_exp_full_database to user_name;

Step 4:  Export Tablespace

‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt. 

expdp user_name/user_password directory=directory_object_name dumpfile=dump_file_name.dmp log=log_file_name.log tablespace=tablespace_name

In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.

4. Export Table

Tables can be exported using the ‘expdp’ command.

Let’s see what you will cover here:

1. Create A Directory
2. Create A Directory Object
3. Grant Permission To The User
4. Export Table

Step 1: Create A Directory

This step should be done by a privileged user only on the server. 

‘E:OracleExportExport Tables’

Step 2: Create A Directory Object

A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.

To create a directory object follow these steps:

a. Open command prompt and open sqlplus as ‘sys’. 

sqlplus / as sysdba

  b. Create an object for the directory by executing the following command.

create directory directory_object_name as 'location';

Step 3: Grant Permission To The User

Grant permission, such as read and write on the directory to the user who wants to export the data.

grant read,write on directory directory_object_name to user_name;

Grant export privilege to the user using ‘datapump_exp_full_database’.

grant datapump_exp_full_database to user_name; 

Step 4: Export Table

‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt. 

expdp user_name/user_password directory=directory_object_name dumpfile=dump_file_name.dmp logfile=log_file_name.log tables=table_name

In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.

5. Export A Table Using PAR file 

PAR file is a parameter file with .par extension. You can use this file in your ‘expdp’ command.

Let’s see what you will cover here:

1. Create A Directory
2. Create A Directory Object
3. Grant Permission To The User
4. Create A PARFILE
5. Export Table

Step 1: Create A Directory

This step should be done by a privileged user only on the server. 

E:OracleExportExport Table

Step 2: Create A Directory Object

A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.

To create a directory object follow these steps:

a. Open command prompt and open sqlplus as ‘sys’. 

sqlplus / as sysdba

  b. Create an object for the directory by executing the following command.

create directory directory_object_name as 'location';

Step 3: Grant Permission To The User

Grant permission, such as read and write on the directory to the user who wants to export the data.

grant read,write on directory directory_object_name to user_name;

Grant export privilege to the user using ‘datapump_exp_full_database’.

grant datapump_exp_full_database to user_name;

Step 4: Create A PARFILE

First, identify the location of your parfile. You can choose your desired location. 

‘E:OracleExport’

Create a text document and save the name with .par extension.

‘par_file_name.par’

In the par file, specify the directory object name, dump filename, log filename, and the name of the tables that you want to export.

directory=directory_object_name
dumpfile=dump_file_name.dmp 
logfile=log_file_name.log 
tables=table_name

Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_name.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation. Tables are used to define the name of the tables which you want to export.

Step 5:  Export Table

‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt. 

expdp user_name/user_password parfile='location’;

In the given command, ‘expdp’ performs the export operation. ‘user_name/user_password’ is used to login to the database. Parfile determines the location of the parameter file from where they can fetch the directory object, dump file, log file, and table names.

Conclusion

The Data Pump export utility ‘expdp’ is used to make a copy of the data and metadata and store them in operating system files, called as dump files (extension .dmp). These dump files have disks that contain table data, database metadata, etc. But using expdp utility to export data from the Oracle database is a complex and time-consuming process. To avoid all these challenges, you can directly opt for a fully automated No-code Data Pipeline, Hevo. Hevo will not only migrate your data from the Oracle database to your desired location but will also make sure that your data is safe and consistent. 

Visit our Website to Explore Hevo

Give Hevo a try by and Sign Up up for a 14-day free trial today.

Let’s know about your experience of exporting your data from Oracle in the comment section below.