Oracle Data Pump Export: Unload Data Instantly

on Data Integration • September 29th, 2021 • Write for Hevo

Are you struggling to export your data from Oracle? Does this step make you apathetic in your work? If your answer is yes then you have landed on the right page. This blog will take you through the various ways by which Oracle Data Pump exports data from your database with necessary screenshots.

Table of Contents

Introduction to Data Pump

Oracle offers the export and import utility to its customers in the form of its Data Pump. Oracle versions 10g and above can use this feature. Data Pump offers the high-speed movement of data and metadata from one database to another. 

Oracle Data Pump Export utility is invoked using expdp and Oracle Data Pump Import is invoked using impdp. Your database, tablespace, schema or table can be exported using Oracle Data Pump Export.

Difference Between Data Pump and Export/Import Utility

The differences between Oracle Data Pump Export/Import and traditional Export/Import are listed below:

  • Data Pump offers Export and Import utility by Oracle. This method is more flexible and faster than traditional Export and Import.
  • Data Pump operates on dump files whereas the traditional method involves a single file. 
  • Data Pump offers better performance as expdp and impdp use parallel execution during processing whereas the traditional method uses only a single execution during processing.
  • Data Pump is a utility offered by the server whereas the conventional method allows access from both client and server-side.

Prerequisites

  • Oracle database version 10g or above. Find the newest versions of the Oracle database here.
  • Knowledge and experience in RDBMS.

Methods for Oracle Data Pump Export 

Data Pump Export in Oracle can be performed using three different platforms as listed below:

Method 1: Oracle Data Pump Export in SQL Developer
This method provides a step by step guide for Oracle Data Pump Export in SQL Developer. Relevant screenshots will help you understand the process conveniently. 

Method 2: Oracle Data Pump Export in SQL *Plus
This method provides you with a detailed guide about the Oracle Data Pump Export in SQL *Plus.  

Method 3: Using a No-code Data Pipeline, Hevo
Hevo handles all your data migration for you. You no longer have to export data periodically. Hevo will move your data to any database or data warehouse of your choice in real-time.

Sign up here for a 14-day free trial!

Method 1: Oracle Data Pump Export in SQL Developer

Let’s discuss the Data Pump Export process in SQL Developer.

  1. Connect to your SYS Database
  2. Grant Access to your Database
  3. Create a Directory Object for your Database
  4. Open Data Pump Export Wizard of your Database
  5. Export your Database 

Step 1: Connect to your SYS Database

Connect to a privileged user such as a system or sys. 

Oracle Data Pump Export:
Image Source

Step 2: Grant Access to your Database

Grant DBA access to the user, who contains your desired database, schema, tablespace or table by executing the following query in the worksheet.

GRANT DBA TO username;  
Oracle Data Pump Export : Granting Access to Database
Image Source: Self

Step 3: Create a Directory Object for your Database

A directory object is like a pointer pointing to the directory. Create an object for the directory using the following command:

CREATE OR REPLACE DIRECTORY directory_object_name AS directory_location;

Step 4: Open Data Pump Export Wizard of your Database

In the menu bar, select View and click on the DBA option.

Oracle Data Pump Export : opening DBA from View in SQL Developer
Image Source: Self

Select the new connection option in the DBA panel and then select the connection containing your desired data. Provide the correct credentials and open the database with your data.

Oracle Data Pump Export - Selecting New Connection
Image Source

In the DBA panel expand your connection and then select Data Pump. Right-click on Data Pump and select Data Pump Export Wizard. 

Oracle Data Pump Export Wizard in SQL Developer
Image Source

Step 5: Export your Database 

In the Export Wizard, select the option which you want to export. The types that can be exported using Data Pump are as follows:

  1. Database
  2. Tablespace
  3. Schema
  4. Table

Select Next and proceed.

Oracle Data Pump Export - Selecting Schema Source
Image Source

Select the schema which you want to export and then select ‘>’ in order to include it in the process of exporting.

Oracle Data Pump Export - Selecting Schemas
Image Source

Similarly, select the name of the tables which you want to export and click the down arrow. If you want to export all the tables of that schema then select the double down arrow and click next.

Oracle Data Pump Export - Filtering
Image Source
Oracle Data Pump Export - Choosing Table Data
Image Source

In the Options, select the correct directory object name in the Log File drop-down. You can also change the name of the log file. 

Oracle Data Pump Export - Configuring Options
Image Source

In the Output Files, select the correct directory object name in Directories drop-down. You can also change the name of the dump file.

Oracle Data Pump Export - Choosing Output Files
Image Source

Click Finish.

Oracle Data Pump Export - Reviewing Summary of Export
Image Source

Method 2: Oracle Data Pump Export in SQL *Plus

 If you are a SQL *Plus user then you can follow these steps for Oracle Data Pump export.

  1. Create a Directory Object for your Directory
  2. Grant Permission to your User
  3. Export your Data

Step 1: Create a Directory Object for your Directory

A directory object is like a pointer pointing to the directory. This step should be done by the privileged users only for example, ‘sys’ user. 

In the command prompt, write the following command in order to connect to SQL Plus.

sqlplus / as sysdba
Oracle Data Pump Export - Connected to Oracle Database

Create an object for the directory by using this command:

CREATE DIRECTORY directory_object_name AS location; 
Oracle Data Pump Export - Creating Directory for Export in Local System
Image Source: Self

Step 2: Grant Permission to your User

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

GRANT READ, WRITE ON DIRECTORY directory_object_name TO username;
Oracle Data Pump Export - Granting Read and Write Access on Directory
Image Source: Self

Grant export privilege to the user using the following command.

GRANT DATAPUMP_EXP_FULL_DATABASE TO username;
Oracle Data Pump Export - Grant export privilege
Image Source: Self

Step 3: Export your Data

Exit from SQL Plus and write the following command in the command prompt which uses ‘expdp’.

expdp username/password DIRECTORY=directory_object_name DUMPFILE=dump_file_name.dmp LOGFILE=log_file_name.log SCHEMAS=schema_name;

Hevo Data – An Efficient Alternative to Export Data

Hevo is a No-code Data Pipeline. Hevo helps you export data from Oracle to any destination such as a data warehouse. Hevo integrates data from 100+ sources and loads it into a database or a data warehouse. Hevo offers secure and reliable data transfer at a reasonable price.

Get started with hevo for free

Some of the salient features of Hevo are listed below:

  1. Easy Implementation: Hevo offers hassle-free set-up and implementation in minutes. 
  2. Fully-Automated: Manually loading your data from the source to the destination is a complex and time-consuming process. Hevo makes this process a cake-walk for its customers as it is a fully automated platform. 
  3. Zero Maintenance: Once you are done with the set-up, Hevo takes care of everything. 
  4. No Data Loss: Hevo offers a fault-tolerant algorithm, ensuring that there is no data loss.
  5. Secure: End-to-end encryption and two-factor authentication ensures that your data is secured. 

Experience all these amazing features of Hevo and sign up today for a 14-day free trial.

Conclusion

You have learned how to use Oracle Data Pump Export in two different ways. If you prefer writing queries for your export process then using command-line SQL *Plus is the best way whereas if you don’t want to write queries then working with SQL Developer will be beneficial. 

visit our website to explore hevo

If you want to migrate your data from Oracle to another database or a data warehouse then you should opt for this No-code Data Pipeline, Hevo. Hevo is a fully-automated data pipeline that requires no monitoring from your end.

Let us know about your experience with Oracle in the comments section below.

No-code Data Pipeline for Oracle