Table of Contents

Data Pump Utility

In this tutorial, we are going to describe the oracle data pump utility step by step.

What is Data Pump Utility?

Oracle Data Pump is a very fast and high-speed utility. Through the Data Pump utility, we can move data and metadata from one database to another database very fast.

Oracle Data Pump flexible, faster, and latest alternative to the ‘exp’ and ‘imp’ which are old utilities and used in previous versions. The Oracle Data Pump provides basic export and import functionality via the DBMS_DATAPUMP PL/SQL package.

Data Pump Export and Import interfaces

Command-line
Parameter file
Interactive command line
Database control

Data Pump Export and Import modes

Full
Schema
Table
Tablespace
Transportable tablespace

Table Level Exports and Imports

Connect your sys user for creating a directory which is used to store EXPORT backups and give read, write permission on directory and EXPORT, IMPORT permission to Scott and system users. Which users have all these permissions only those users can use this directory and run the EXPORT and IMPORT command. We have some default Directories if you want to use these Directories then you can, you can check default created directories using the DBA_DIRECTORIES view.

How to create directory in oracle?

Using the following commands we can create a directory.

$ mkdir  /u01/data_pump

$ sqlplus  / as sysdba 

SQL> create directory dir  as ‘/u01/data_pump’;

Now giving permission to Scott and system users to read and write on this directory.

SQL> grant read, write on directory dir to scott,system;
SQL> grant exp_full_database, imp_full_database to scott,system;

Note:- Oracle Data Pump work with physical directories because it is a server-based technology. Oracle Data Pump never writes on your local file system.

In the below example we take an export of two tables emp and dept.

$ expdp scott/tiger tables=emp,dept  directory=dir  dumpfile=emp_dept.dmp logfile=emp_dept.log

Now Drop any one or both tables with purge keyword from Scott user after that recover it using the above backup. Use the following commands.

SQL> drop table emp purge;
SQL> select count(*) from emp; 

Output will come like "error massage table or view does not exists"

How to import table from export backup

Run below command and import table.

$ impdp scott/tiger tables=emp  directory=dir dumpfile=emp_dept.dmp logfile=emp_dept.log

Now check your table it is recovered or not.

Schema Level Export and Import

If you want to Export complete Schema (user) using expdp then you have to run the below command.

Export Schema

$ expdp scott/tiger  schemas=scott,hr  directory=dir  dumpfile=scott_hr.dmp  logfile=scott_hr.log

Now if your user accidentally drops and you have an Export backup of your database then don’t worry you can recover it easily using the following command.

SQL> drop user scott cascade;

SQL> conn scott/tiger 

Above command will show you  "Error massage"

IMPORT Schema

After dropping a schema we can import it using the following command.

$ impdp scott/tiger  schemas=scott  directory=dir  dumpfile=scott_hr.dmp  logfile=scott_hr.log

After importing schema now you can connect with your user.

Full Database Exports and imports

To complete Database Export using the “full=y” parameter, it’ll be indicated complete database Export.

Export full Database

$ empdp system/password  full=y  directory=dir  dumpfile=full_db.dmp  logfile=full_db.log

Import full Database

Now if you lost any important table then you can recover it using this backup with the below command.

$ impdp system/password  full=y directory=y dumpfile=full_db.dmp  logfile=full_db.log

I hope you enjoy this Export – Import Utility tutorial for more details you can watch my YouTube video below.

Read - Export tables from a particular tablespace

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-s ingh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology

Oracle Data Pump Utility