Export and Import

Export and Import

Oracle's export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting (exp), database objects are dumped to a binary file which can then be imported (imp) into another Oracle database.

These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter.

Note: From Ver. 10g Oracle is recommending to use Data Pump Export and Import tools, which are enhanced versions of original Export and Import tools.

Logical Backup can be taken in three methods:

1. Full database level
2. User level
3. Table level(Individual)

Logical Backup provides following advantages:

• It requires very less disk space to export the data
• It is useful to migrate data from one database to another database
• It is useful to improve the database performance by using database reorganization
• It is useful to migrate the data from one oracle version other oracle version
• It is useful to migrate data from one operating system to another os
• It is useful to move tables from one user to another user

Logical backups can be performed by exp and imp tools, and expdp and impdp tools.

Exp & imp tools:

Note: before performing logical backup database instance must be started

$ su – oracle

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>select * from all_users;

Sql>select * from dba_users;

Sql>create user user1 identified by user1;

Sql>grant connect, resource to user1;

How to export complete full database backup:

$exp file = /Oracle/logicalbackup/dbfull.dmp log=dbfull.log full=y

Username : system/manager as sysdba

$ ls dbfull.dmp

How to export user level backup:

$exp file = /Oracle/logicalbackup/user1.dmp log=user1.log

Username = user1/user1

$ ls user1.dmp

How to export individual tables:

$exp file = /Oracle/logicalbackup/user1emp.dmp tables = emp log= emp.log

(OR)

How to export multiple tables

$ exp file=/Oracle/logicalbackup/user1tables.dmp tables = emp,dept,person

Username = user1/user1

$ ls user1tables.dmp

How to import full database:

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>drop user user1 cascade;

$imp file=/Oracle/logicalbackup/dbfull.dmp full = y ignore = y

Note: ignore = y this option cannot export table if already table is available.

Username = system/manager as sysdba

 $sqlplus ‘/as sysdba’

Sql>select * from all_users;

 

 

How to import user1 objects into user2:

$ imp file = /Oracle/logicalbackup/user1.dmp from user = user1 to user = user2

Username = system/manager as sysdba

Username = system/manager as  sysdba

How to import user1 objects into user2 without records:

$ imp file = /Oracle/logicalbackup/user1.dmp from user = user1 to user = user2 rows = n

 

How to import individual tables:

$ sqlplus user1/user1

Sql>drop table emp;

Sql>select * from tab;

Sql>host

$ imp file = /Oracle/logicalbackup/user1emp.dmp tables = emp

Username = user1/user1

$ sqlplus user1/user  

Sql>select * from emp;