Export (exp) and Import (imp)

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.
In order to use exp and imp in Oracle Database you have to run catexp.sql script. catexp.sql basically creates the exp_full_database and imp_full_database roles to the oracle user. We have to run catexp.sql for the time first time if database not create using DBCA. Because it is already executed if you create your database by DBCA.

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”.

Source: $ORACLE_HOME/rdbms/admin/catexp.sql

Full database export:
The EXP_FULL_DATABASE and IMP_FULL_DATABASE respectively, are needed to perform a full export.
Use the full=yes export parameter for a full export.

exp scott/tiger file=emp.dmp full=yes statistics=none
imp system/manager file=emp.dmp fromuser=scott touser=scott

Tablespace:

Use the tablespaces export parameter for a tablespace export. It is only apply to transportable tablespaces.

exp userid=’system/manager’ tablespaces=users file=exp.dmp log=tbs.log statistics=none

Schema:
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

Table:
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.

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

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using a parameter file:
exp userid=scott/tiger@orcl parfile=export.txt

export.txt contains:

BUFFER=100000
FILE=scott_data.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

Take DDL output in Dump file:
imp scott/tiger file= emp.dmp indexfile=emp.txt

or, to see ddl into screen,
imp scott/tiger file=emp.dmp show=y

How can one improve Import/ Export performance?

EXPORT (exp):

• Set the BUFFER parameter to a high value (e.g. 2Mb — entered as an integer “2000000”)
• Set the RECORDLENGTH parameter to a high value (e.g. 64Kb — entered as an integer “64000”)
• Use DIRECT=yes (direct mode export)
• Stop unnecessary applications to free-up resources for your job.
• If you run multiple export sessions, ensure they write to different physical disks.
• DO NOT export to an NFS mounted file system.  It will take forever.

IMPORT (imp):

• Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.
• Place the file to be imported on a separate physical disk from the oracle data files
• Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file
• Set the LOG_BUFFER to a big value and restart oracle.
• Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)
• Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)
• Use COMMIT=N in the import parameter file if you can afford it
• Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics
• Remember to run the indexfile previously created

What are the common Import/ Export problems?

• ORA-00001: Unique constraint (…) violated
  • You are importing duplicate rows. 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
• ORA-01562: Failed to extend rollback segment
  • Create bigger rollback segments or set parameter COMMIT=Y while importing
• IMP-00015: Statement failed … object already exists…
  • Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

 Source www.orafaq.com