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:windowssystem32> exp sagarsagar file = d:datadmpsagar.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:windowssystem32> imp system/**** file = d:datadmpsagar.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:windowssystem32> imp system/**** file = d:datadmpsagar.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:windowssystem32> exp sagarsagar file = d:datadmpsagarveera.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:windowssystem32>  imp system/**** file = d:datadmpsagarveera.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:windowssystem32>  imp system/**** file = d:datadmpsagarveera.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:windowssystem32>  imp system/**** file = d:datadmpsagarveera.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:oracleproduct10.2.0oradataPRODsagar01.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:windowssystem32> exp system/**** file=d:datadmpsagartbs.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:oracleproduct10.2.0oradataPROD” 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:windowssystem32> imp system/**** file=d:datadmpsagartbs.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:windowssystem32> exp sagar/sagar file = ‘D:datadmpsagarschema.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:Windowssystem32>imp system/**** file=d:datadmpsagarschema.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:windowssystem32> exp system/**** full=y file=d:datadmpfulldb.dmp

Go to OS level delete all data files c:oracleproduct10.2.0oradataPROD 

Go to CMD Panel


C:windowssystem32> imp system/****   file=d:datadmpfulldb.dmp

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

Thanks

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: