Oracle Data Pump Utility

In this tutorial we are going to describe 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 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 DBA_DIRECTORIES view.

How to create directory in oracle?

Use following commands we can create directory.

$ mkdir  /u01/data_pump

$ sqlplus  / as sysdba 

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

Now giving permission to scott and system user for 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 write on your local file system.

Tables Level Export – Import

In below example we take 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 table with purge keyword from scott user after that recover it using above backup. Use 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 recover or not.

Schema Level Export

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

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

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

SQL> drop user scott cascade;

SQL> conn scott/tiger 

Above command will show you  "Error massage"

Schema Level IMPORT

After drop a schema we can import it using following command.

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

After import schema now you can connect with your user.

Full Database Exports

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

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

Now if you lost any important table then you can recover it using this backup with 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-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology