Site icon IT Tutorial

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-1


In this article I will explain the data pump technology which we use frequently in Oracle databases. This technology is very often used by Oracle DBAs because it provides very effective Table and schema based backups which we cannot do with RMAN.


We use data pump technology to move data from a database to a different database. The data pump is also a nice technology in schema and table-based backup. The data pump is also a nice technology for schema and table-based backup.

Although the Oracle database backup tool is RMAN (Recovery Manager), RMAN may be inadequate in some issues. Oracle datapump helps in this case. We cannot do this with RMAN when we want to move a table or schema between 2 different databases, but we can do it with data pump.


With the data pump, only tables and schema can be moved in full database and tablespace. With the data pump technology, we can export the data from a database and import it into the other database. For this, Oracle offers expdp for export and impdp tool for import.



I will now describe the use and examples of expdp and impdp commands below.


Expdp: You can take export of the table (s), schema (s) and tablespace (s) with this command. These export files are also used frequently in Backup solutions because they provide redundancy. A user must have EXP_FULL_DATABASE privileges to export the data related to this command.


IMPDP: You can import the table (s), schema (s) and tablespace (s)  to the target database with this command. A user must import IMP_FULL_DATABASE privileges to import export files with this command.

We can run these two commands on the operating system and perform the related operations. The data is taken from Export is kept with .dmp at the specified path. If you do not create a directory on the operating system for the data pump, the export files that are taken by default are discarded to the path under parameter of DATA_PUMP_DIR directory. We can create a separate directory for datapump on the operating system as follows.


create directory DATAPUMP as '/backup';

Related (sys,system) User has to have read / write permission on the related directory . We should grant read and write permission like following

grant read, write on directory DATAPUMP to sys;



Next article link is as follows.

Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-2


Do you want to learn Oracle Database for Beginners, then read the following articles.

 2,948 views last month,  4 views today

Exit mobile version