EXPORT AND IMPORT UTILITIES

EXPORT AND IMPORT UTILITIES

CH # 18

Export Full Schema
C:\Documents and Settings\Shahid ul ghani>exp
Username: scott
Password: tiger
Enter array fetch buffer size: 4096 > 4096
Export file: EXPDAT.DMP > c:\bac.dmp
(2)U(sers), or (3)T(ables): (2)U > u (if we want to export table then use T)
Export grants (yes/no): yes > y
Export table data (yes/no): yes > y (if No then only definition will be exported)
Compress extents (yes/no): yes > y
. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS 0 rows exporte
. . exporting table DEPT 4 rows exporte
. . exporting table EMP 14 rows exporte
. . exporting table SALGRADE 5 rows exporte
.
Export terminated successfully without warnings.
Export a specific Table or tables
C:\> exp scott/tiger tables=emp,dept
Export: Release 10.2.0.1.0 - Production on Sat Nov 8 17:19:23 2008
About to export specified tables via Conventional Path ...
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
Export a Particular Tablespace

SQL>Alter tablespace users Read Only
C:\> exp userid=’sys/oracle as sysdba’ tablespaces=users file=c:\users.dmp
(if we want to export only single datafile then use the following )
Datafile=’c:\oracle\oradata\orcl\users01.dbf’
SQL> Alter tablespace users Read Write
Export Full database by SYS
C:\Documents and Settings\Shahid ul ghani>exp ‘sys/oracle as sysdba’ full=y
file=c:\fulldb.dmp rows=y (if no then tables structure is export)
INCREMENTAL EXPORT
There are two major types of incremental export .
1- Incremental
2- Full / Non- Incremental
There are three types of incremental export
1- Complete
2- Incremental
3- Cumulative
C:\> EXP system/manager FILE=C:\complete.dmp FULL=y INCTYPE=complete
C:\> EXP system/manager FILE=c:\increment.dmp FULL=y INCTYPE=incremental
C:\> EXP system/manager FILE=c:\cumulative.dmp full=y INCTYPE=cumulative
IMPORT UTILITY
C:> imp scott/tiger tables=emp rows=y file=c:\imp.dmp
C:\> imp system/manager FROMUSER=scott file=c:\abc.dmp TOUSER=hr
C:\> imp userid=’sys/oracle as sysdba’ tabelspace=users file=c:\users.dmp