Thursday, October 4, 2012

How to Export and Import commands works for Oracle 10g XE


Following are the commands for performing Export and Import a backup file form one system to another by using command prompt. Here we are taking Oracle 10g express edition as an example.

Export command
  
 c:\users\Sone>EXP SYS/SYS TABLES=EMPLOYEES,DEPARTMENTS FILE=EXP1.DMP  
 USERNAME:SYS/SYS AS SYSDBA 


Export Manual way
  
 c:\users\Sone>EXP  
 USERNAME:HR/HR  
 Enter Array fetch buffer size:4096>4096  
 Export File:EXPDAT.DMP>EXP1.DMP  
 (2)U(sers),or(3)T(ables):(2)U>t  
 Export table data(yes/no):yes>y  
 Coompress extents(yes/no):yes>y  
 Table(T) or Partition(T:P) to be exported:(Return to quit)>EMPLOYEES,DEPARTMENTS  
 Export terminated successfully.  


Copy EXP1.DMP from Sone(System one) to Stwo(System two)

Import Command
 
 c:\users\Stwo>IMP SYS/SYS FILE=c:\users\Stwo\EXP1.DMP TABLES=EMPLOYEES,DEPARTMENTS FROMUSER=SYS  
 USERNAME:SYS/SYS AS SYSDBA  
 Import terminated successfully.  

NOTE: Here we are exporting dmp file from hr and sys schema but importing only in sys schema.It is because employees and departments tables are already present in hr schema.

No comments:

Popular Posts