Export Tablespace Using Expdp

Hi, In this article we are going to learn how to export tablespaces using expdp data pump utility practically.

What is tablespace?

A tablespace is a logical unit of storage that is used by the database to store database objects like tables and PL/SQL codes.

We can export tablespaces using the expdp utility which is also called logical backup of the tablespace in the Oracle database. The export backup of tablespace contained all the object's data with metadata. So let's do the practical how we can take the export backup of tablespace using the expdp utility.

Before taking export backup of tablespace we need to check some information about the tablespaces like how many tablespaces we have with the help of “v$tablespace”. Login in your database as sysdba then run the following command which displays all tablespace names.

SQL> select name from v$tablespace;

Steps to take export backup using data pump utility

There are only four steps to take backup.

1. Create OS level Directory
2. Create directory object
3. Grant mandatory privileges
4. Export the tablespace

1. Create OS level Directory

To take the export backup we need a place where our export backup saves, so you can create a folder on any location in your system. The directory must be available on the database server not on the client machine. In my case, I create a directory (folder) by the name "shripal" inside "/u01/" so my directory location is: "/u01/shripal"

2. Create Directory object

Login in your database as a sys user runs the following command to create a logical directory object in the database.

$sqlplus / as sysdba

SQL> create directory exp_tab as '/u01/shripal';

In the above query, the exp_tab is the name of a directory object, you can give any name here as per your requirement. During the export, we are using the same name as our directory which is pointing to the physical directory (/u01/shripal).

3. Grant mandatory privileges

If you are taking the export backup using sys user then you do not need to grant any privileges to anyone. But if you are going to take export backup using another schema then you should grant read, write and export, import privileges to that user.

Suppose in my case I have a user "DIGITAL" so we need to grant the below privileges to this user.

SQL> grant read, write on exp_tab to digital;

Grant Export and Import Privileges to user

SQL> grant exp_full_database, imp_full_database to digital;

4. Export the Tablespace

At this point we have done all the required settings, run the following command to take an export backup of the tablespace.

$expdp digital/digital@db12c directory=exp_tab dumpfile=tablespace_bkp.dmp logfile=tablespace.log tablespaces=users

The above command must be run on $ prompt.

Let's understand the above export tablespace query:

EXPDP - expdp is a command which is called the datapump utility with the user credentials through which user you want to perform the export followed by SID (@db12c), SID is optional but that is a good practice.

DIRECTORY - The directory parameter specifies the name of the directory object which we created in the second step. Which confirms the location where all the backup files are stored.

DUMPFILE - The dumpfile parameter specified the name of the dumpfile which stores all the exported data, in my case I give the name of the dumpfile as "tablespace_bkp.dmp". The dumpfile is a binary file.

LOGFILE - The logfile parameter defines the export log name, you can give any name here. The log file is a text file that is human-readable.

TABLESPACE - The tablespace parameter has identified the name of the tablespaces which you want to take export backup and it's confirmed to data pump utility run in tablespace mode. Here you can specify multiple tablespace names separated by comma (,).

Read - Check Datapump dump file is corrupted or not