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. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.
The export/import utilities are commonly used to perform the following tasks:
- Backup and recovery (small databases only, say < +50GB, if bigger, use RMAN instead)
- Move data between Oracle databases on different platforms (for example from Solaris to Windows)
- Reorganization of data/ eliminate database fragmentation (export, drop and re-import tables)
- Upgrade databases from extremely old versions of Oracle (when in-place upgrades are not supported by the Database Upgrade Assistant any more)
- Detect database corruption. Ensure that all the data can be read
- Transporting tablespaces between databases
Export Examples
$> exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
$> exp scott/tiger file=emp.dmp tables=(emp,dept)
$> exp scott/tiger tables=emp query=”where deptno=10″
$> exp scott/tiger file=abc.dmp tables=abc query=\”where sex=\’f\’\” rows=yes
Import Examples
$> imp scott/tiger file=emp.dmp full=yes
$> imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept
Note : It is generally advised not to use exports as the only means of backing-up a database. Physical backup methods (for example, when you use RMAN) are normally much quicker and supports point in time based recovery (apply archivelogs after recovering a database). Also, exp/imp is not practical for large database environments.