IMPORT / EXPORT Basic

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.

Basic Syntax :
exp username/password file=Filename.dmp log=anyname.log full=yes /table=emp


exp username/password:
Login with Username From which you want to export some table .
For example : exp scott/tiger ..-

file=Filename.dmp:
Enter any desired name ,with .dmp extension , it will create a file where your exported table or schema will store in binary form .
-
log=aaa.log:
Its optional ,but its useful if you use create logile ,it will prevent you from future problems .

-
full=yes /table=emp:
It will export your whole schema objects , you can also specify a table name


.
Example :
Following is basic and very simple syntax ..

Exp scott/tiger file=abcXyz.dmp tables=emp ;
.

There are More advancements in imp/exp , for example you can export a particular department
.
Example: .

The Following statement will export only those rows from emp table which deptno=10 .
exp scott/tiger file=practice01.dmp tables=emp query= "where deptno=10"
.

Common IMPORT/EXPORT Errors :

ORA-00001: unique constraint violated (.
OR
IMP-00015:following statement failed because the object already exists:

This error is issued if a user tries to insert an already existing value into a column defined unique.
Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created)

ORA-01555: Snapshot too old

Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO

Final Synatx :
exp scott/tiger file=empdept.dmp log=file.log tables=(emp,dept) rows=yes indexes=no