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
- Parameter file
- Interactive command line
- Database control
Data Pump Export and Import modes
- 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.
$ 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"
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.
Connect with me on: