Oracle Export and Import Utility ( Table level, Tablespace level, Schema level and full database)

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


Look for the “imp” and “exp” executables in your $ORACLE_HOME/bin directory. These parameters can be listed by executing the following commands: “exp help=yes” or “imp help=yes”.

Table Level EXPORT AND IMPORT ( to same User)

Login as SQL*PLUS:


SQL> conn / as sysdba

SQL> create directory datapump as 'D:\datadmp';

SQL> create user sagar identified by sagar default tablespace users;

SQL> Grant connect, resource to sagar;

SQL> grant read, wirte on directory datadmp to sagar, scott;

SQL> conn sagar/sagar

SQL> create table sagar (name varachar(10), no number(2));

Insert values into sagar

SQL> insert into sagar values (sagar, 1);

SQL> /

SQL> /

SQL> /

SQL> commit;

Go to command Prompt icon right click on it. Click on Run as Administrator

C:\windows\system32> exp sagar\sagar file = d:\datadmp\sagar.dmp  tables=sagar

Go to SQL* PLUS

SQL>  conn sagar/sagar

SQL>  select * from sagar;

SQL>  drop table sagar purge;

SQL> select * from sagar;

Error

Go to command Prompt icon right click on it. Click on Run as Administrator

C:\windows\system32> imp system/**** file = d:\datadmp\sagar.dmp fromuser=sagar touser=sagar

Go to SQL* PLUS

SQL>  conn sagar/sagar


SQL>  select * from sagar;


=>  four rows selected

Table Level EXPORT AND IMPORT (to Ddifferent User)

We have .DMP file of sagar table. Now we can import that table to Scott User. By using following syntax

Go to CMD Panel

C:\windows\system32> imp system/**** file = d:\datadmp\sagar.dmp fromuser=sagar touser=scott

Go to SQL* PLUS



SQL>  conn scott/tiger



SQL>  select * from sagar;


=>  four rows selected


Multiple Tables EXPORT AND IMPORT 

Go to SQL* PLUS

SQL>  conn sagar/sagar

SQL> create table veera (name varachar(10), no number(2));

Insert values into veera

SQL> insert into veera values (sagar, 1);

SQL> /

SQL> /

SQL> /

SQL> commit;

SQL>  select * from sagar;

=>  four rows selected

SQL>  select * from Veera;


=>  four rows selected


Exporting multiple tables


Go to CMD Panel


C:\windows\system32> exp sagar\sagar file = d:\datadmp\sagarveera.dmp  tables=sagar,veera





Go to SQL* PLUS


SQL>  conn sagar/sagar


SQL>  select * from sagar;


=> four rows selected


SQL>  drop table sagar purge;


SQL> select * from sagar;


Error



SQL>  select * from veera;


=> four rows selected


SQL>  drop table veera purge;


SQL> select * from veera;


Error





Importing to same user


Go to CMD Panel


C:\windows\system32>  imp system/**** file = d:\datadmp\sagarveera.dmp fromuser=sagar touser=sagar




Go to SQL* PLUS



SQL>  conn sagar/sagar


SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected





Exporting multiple tables


Since we have .dmp file of sagar, veera tables, now we need to import those two tables to scott schema.


In order to do that you need to connect as scott schema first and delete the sagar table what we imported before



Go to SQL* PLUS


SQL>  conn scott/tiger


SQL>  select * from sagar;


=> four rows selected


SQL>  drop table sagar purge;


SQL> select * from sagar;


Error


Importing multiple tables to different user


Go to CMD Panel


C:\windows\system32>  imp system/**** file = d:\datadmp\sagarveera.dmp fromuser=sagar touser=scott





Go to SQL* PLUS


SQL>  conn scott/tiger




SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected




Importing particular table to same or different users



SQL>  conn sagar/sagar




SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected




SQL> drop table veera purge;


=> Table dropped



Importing particular table to same User (or Different)


Go to CMD Panel


C:\windows\system32>  imp system/**** file = d:\datadmp\sagarveera.dmp tables= veera fromuser=sagar touser=sagar (or scott)





Go to SQL* PLUS


SQL>  conn scott/tiger




SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected



======================================================================




Tablespace level export and import


Go to SQL* PLUS


SQL> conn / as sysdba


(create a tablespace name sagar)


SQL> create tablespace sagar datafile 'c:\oracle\product\10.2.0\oradata\PROD\sagar01.dbf ' size 10M;

=> tablespace created


( Assign previously created sagar user to sagar's tablespace)


SQL> alter user sagar default tablespace sagar;


SQL> disconnect


SQL> conn sagar/sagar



SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected



SQL>  create table sam as select * from veera;


=> table created


SQL> commit;

SQL>  select * from sam;


=> four rows selected


Note: Tables sagar and veera are stored on users tablespace and table sam stored at sagar's tablespace




Go to CMD Panel


C:\windows\system32> exp system/**** file=d:\datadmp\sagartbs.dmp tablespaces = sagar




Go to SQL* PLUS


SQL>  conn  / sysdba


SQL>  drop tablespace sagar including contents;


=> Tablespace droped


( NOTE: Delete sagar tablespaces corresponding datafile at OS level 
Go to "c:\oracle\product\10.2.0\oradata\PROD\" directory and delete sagar01.dbf )


SQL>  conn sagar/sagar



SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected



SQL> select * from sam;


Error


Importing tablespace


Go to CMD Panel


C:\windows\system32> imp system/**** file=d:\datadmp\sagartbs.dmp tablespaces = sagar


Go to SQL* PLUS




SQL>  conn sagar/sagar



SQL>  select * from sam;


=> four rows selected


You can export import multiple tablespace's also By giving "tablespaces = sagar, users"


======================================================================




Schema Level Exports and Imports




Go to SQL* PLUS


SQL> conn sagar/sagar;



SQL>  select * from sagar;


=> four rows selected




SQL>  select * from veera;


=> four rows selected




SQL>  select * from sam;


=> four rows selected



Exporting full schema


Go to CMD Panel


C:\windows\system32> exp sagar/sagar file = 'D:\datadmp\sagarschema.dmp owner=sagar




Go to SQL* PLUS


SQL> conn sagar/sagar;


SQL> drop table sagar purge;



SQL> drop table veera purge;


SQL> drop table sam purge;


SQL> select * from tab;


=> no rows selected


Note: Delete tables permanently from recycle bin also





Go to CMD Panel


C:\Windows\system32>imp system/**** file=d:\datadmp\sagarschema.dmp full=y touser = sagar



Go to SQL* PLUS


SQL> conn sagar/sagar;




SQL>  select * from sagar;


=> four rows selected


SQL>  select * from veera;


=> four rows selected

SQL>  select * from sam;


=> four rows selected


NOTE: You can import this schema to any user.. instead of sagar In the place of to user you can use scott, hr


FULL DATABASE EXPORT AND IMPORT



Go to SQL* PLUS


SQL> conn sagar/sagar;




SQL>  select * from sagar;



Go to CMD Panel


C:\windows\system32> exp system/**** full=y file=d:\datadmp\fulldb.dmp

Go to OS level delete all data files c:\oracle\product\10.2.0\oradata\PROD 



Go to CMD Panel


C:\windows\system32> imp system/****   file=d:\datadmp\fulldb.dmp 


You can take particular table particular schema, particular tablespace from fulldb.dmp


Thanks
Sagar