Exports

EXP should no longer be used as a backup tool but it is useful for:
Large Exports
IMP/EXP is no longer a Reorganisation Tool
Using IMP / EXP across versions
How to use EXP
Where did my indexes go?
EXP creates a binary file which is transportable across operating systems - you can transport it from Sun Solaris to Windows and it will still work.

Export is useful for detecting physical or logical corruption in the database.
If you use EXP to perform a full database export it will find almost any logical dictionary corruption and it will scan all the rows in every table, finding any bad blocks. It will not find logical corruption such as indexes that point to non-existent rows.
After you have done the export check the log files and do an import with SHOW=Y, this will create a large file of all DDL ("" and broken lines are a problem but...).

EXP should no longer be used as a backup tool but it is useful for:
Detecting inconsistencies (as above)
rebuilding instances (less than 15G)
Copying data between platforms, creating a DMP file on one platform and it can be e-mailed and imported at another.
Its good for extracting DDL.
Cloning schemas.
Transporting tablespaces (8i and higher).

Large Exports
EXP is normally limited by the 23bit OS limit of file size up to 2GB. There are a number of solutions for this:
Use the FILESIZE parameter - available from 8i, to export to a series of files, no large than 500MB each
exp file=f1,f2,f3,f4,f5 filesize=500m
Unless you know approx what size the export will be it will not work. To import this the files must be listed in the order they need to be applied.

Export to an OS pipe - This works on UNIX.
Export to an OS pipe (UNIX)

Subset data - Available from Oracle 8i. You can place a QUERY parameter, which specified a where clause
Put the command in a PARFILE as operating systems do not like the special characters contained in them
Eg. QUERY="where object_id < 5000"


IMP/EXP is no longer a Reorganisation Tool.
From Oracle 8i you do not need to use imp/exp as a reorganisation tool (overcoming fragmentation), you can use ALTER TABLE MOVE command to move tables from tablespace to tablespace, change their storage characterist

Using IMP / EXP across versions
You need to use the correct versions of IMP and EXP to do this.
Use the version of IMP that matches the database your importing into.
Use the version of EXP that matches the lowest of the two versions.
So if you are moving data from Oracle 8.1.5 to version 7.3.4 both IMP and EXP should be 7.3.4 versions.

How to use EXP
exp username/password@instance parfile=xxxxx

BUFFER size of data buffer
COMPRESS import into one extent (Y)
CONSTRAINTS export constraints (Y)
CONSISTENT cross-table consistency
DIRECT direct path (N)
FEEDBACK display progress every x rows (0)
FILE output files (EXPDAT.DMP)
FILESIZE maximum size of each dump file
FULL export entire file (N)
GRANTS export grants (Y)
INCTYPE incremental export type
INDEXES export indexes (Y)
OWNER list of owner usernames
LOG log file of screen output
PARFILE parameter filename
RECORD track incr. export (Y)
ROWS export data rows (Y)
STATISTICS analyze objects (ESTIMATE)
TABLES list of table names
TRIGGERS export triggers (Y)
USERID username/password
QUERY select clause used to export a subset of a table
VOLSIZE number of bytes to write to each tape volume
The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TABLESPACES list of tablespaces to transport


Where did my indexes go?
Exp does not export the index definition for indexes that have system-generated names. It relies on the fact that the index is created implicitly upon object creation (if at all), if an index already exists that will do the job, the system generated one is not created.