Import Export by imp/exp and impdp/expdp

Contents:- Import/Export by imp/exp and impdp/expdp

Exp/imp and Expdp/impdp Concepts and Real-life Scenerios

 ü  Export/ import is used to take logical backup of your database.

 ü  Logical backup can be taken at following level

o   Table Level

o   Tablespace Level

o   User/ Schema level

o   Database Level

 ü  Exp/expdp is used for taking the backup.

 ü  Imp/impdp is used for restore the backup.

 ü  Export/import is perform at O.S. Level and DB Should be up and running.

Exp Parameter/Attributes:

          Userid:        username and password of an user performing the export.

          File:             dumpfile (default expdat.dmp) binary format.

          Tables         Name of the tables to be exported.

          Direct=y      (n) Skips the Buffer Area

          Parfile=       parameter filename

          Constratins=y/n

          Indexes=y/n

          Grants=y/n

          Owner:        name of an owner.

          Log             logfile name

          Tablespaces=name of tablespace

          Transport_tablespace=y

          Full=n/y     (full database)

          Query=subset of table

          Inctype= complete/ incremental / cumulative

IMP Parameter/Attributes:

          Userid:-       username and password of an user performing import.

          File=            exported dumpfile name

          Tables=       name of tables

          Fromuser=  source user

          Touser=      destination user

          Show=y       (shows the contents of dumpfile) data will not be imported.

          Log=                     name of logfile

          Ignore=y              structure will ignore. And rows will be imported.

          Rows=n                structure will be imported but rows will not be imported.

          Indexes=y

          Indexfile=            file that contain syntax to create index.

          Inctype=              system/restore

          Transport_Tablespace=y

          Tablespace=         name of tablespace

          Datafiles=             name/location of datafiles

Two Database.

Source:-      orcl

Target:-      demo

Scenario 1:- Move one Object from one schema to another schema with no rows.

Source Database: orcl

Here we move objects from scott schema to hr schema in orcl database with no rows.

Steps

$>. Oraenv

          Orcl

$> exp hr/hr file=/u01/logbk/employees_nr.dmp rows=n grants=y tables=employees

$> imp scott/tiger file=/u01/logbk/employees_nr.dmp  grants=y tables=employees fromuser=hr touser=scott

$> sqlplus scott/tiger

Sql> select * from employees;

Scenario 2:- Move one Object from one schema to another schema with rows.

Source Database: orcl

Here we move objects from scott schema to hr schema in orcl database with rows.

Steps

$> . oraenv

          Orcl

$> exp hr/hr file=/u01/logbk/employees_r.dmp grants=y rows=y tables=employees

$> imp scott/tiger file=/u01/logbk/employees_r.dmp grants=y rows=y tables=employees fromuser=hr touser=scott ignore=y

$> sqlplus scott/tiger

Sql> select * from employees;

Scenario 3:- Move all Objects from one Schema to another schema.

Source database:- orcl

First Create one new user in which you want to import the data.

$> sqlplus sys as sysdba

Sql> create user demo identified by demo;

Sql> grant create session,resource to demo;

By using exp/imp

Now export all objects of an hr schema

$> exp hr/hr file=/u01/logbk/full_hr.dmp grants=n

Now import all objects of an hr schema into newly created schema demo

$> imp demo/demo file=/u01/logbk/full_hr.dmp grants=y fromuser=hr touser=demo

By using expdp/impdp

First Create Directory referring to Operating system directory

Sql>create or replace directory logbk as ‘/u01/logbk/’;

Sql> grant read,write on directory logbk to hr,scott;

Now export all objects of an hr schema

$expdp hr/hr directory=logbk dumpfile=full_hr.dmp

Now import all objects of an hr schema into newly created schema demo

$>impdp demo/demo directory=logbk dumpfile=full_hr.dmp remap_schema=hr:demo

Scenario 4:- Import all object of source database stc into target database schema new by using network mode

Source Server:- stc

Target Server:- demo

Sql> create user new identified by new;

Sql> grant connect,resource,create session,create database link to new;

Create directory in demo database refer to OS File location.

Sql> create or replace directory logbk as ‘/u01/logbk’;

Sql> grant read,write on directory logbk to new;

Sql>conn new/new

Now create database link to access objects of hr schema of source database stc

Sql> create database link dblink connect to hr identified by hr using ‘stc’;

Now import all objects of hr schema of stc database into new schema of demo database.

$> impdp new/new directory=logbk network_link=<db_link_name> remap_schema=hr:new