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
- 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 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.
Connect with me on: