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.

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 Tablespace
• Export Table
• Export 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.

Also read about How to Enable Oracle Change Tracking? 2 Easy Methods

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.

An Example of Using Oracle Data Pump Export: How to Perform a Table Mode Export

This example demonstrates a table-mode export with the TABLES argument.

In this example, the Data Pump export command exports the employee and job tables from the human resources (hr) schema.

Because user hr is exporting tables from his own schema, it is unnecessary to mention the schema name for the tables. The NOLOGFILE=YES argument indicates that an export log file for the operation is not generated.

Execute the following command:

expdp hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=YES

Read about Working With Oracle Redo Logs: A Comprehensive Guide 101

How to Schedule a Data Pump Export in crontab

Use the following command to create a file containing the expdp script

vi daily_export.sh

export DATE=$(date +%m_%d_%y_%H_%M)
export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

$ORACLE_HOME/bin/expdp username/password@sid directory=export_dir dumpfile=backup_$DATE.dmp logfile=backup_$DATE.log full=y

Run the following example to grant permissions to execute the file above


chmod 755 daily_export.sh


Finally, run the following command to schedule an export in crontab


crontab –e –u oracle

0020***/home/oracle/backup_script

Major Differences Between Data Pump and Original Export/Import Tools

Oracle highly recommends utilizing Data Pump export/import utilities rather than the original export/import tools. The following are the major differences between Data Pump and the original exp/imp tools.

• Data Pump utilities are self-tuned. The original export/import contains additional parameters to adjust the job (recordlength, buffer).
• If any row violates an active constraint while being imported into an existing table using Data Pump, the task is aborted. The original export tool logs the violated row and proceeds to load the remaining data.
• If the main table is compressed, the Data Pump importer will compress the data automatically. The original import utility does not compress the data during the import process.
• Data Pump tools employ parallel execution rather than a single stream of execution.
• Data Pump tools can access files on the server, which improves work performance.
• Data Pump supports character set conversion.
• Using interactive mode in Data Pump, you can stop, start and kill the job and change Data Pump’s interactive mode allows you to halt, start, and kill jobs, as well as adjust parameters while they are executing. Data Pump jobs can be restarted with no loss of data.
• Data Pump allows you to estimate job time without loading or unloading data.
• New remapping settings introduced to the Data Pump import make it more powerful than the original import.

Conclusion

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. Check out Hevo pricing to choose the best plan for your organization.

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

References:

• Table mode export
• Data pump export in crontab