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
No comments:
Post a Comment