Export / Import Utility

- Command line tools used to extract tables, schemas, or entire database definitions from one Oracle instance, to be imported into another instance or schema
- Export (exp) and Import (imp) utilities is one the easiest and oldest method to move the data between database instances.
- Export (exp) and import (imp) utilities are used to perform logical database backup and recovery
- Export file is an Oracle binary-format dump file and can only read by the import utility
Can find the help using exp help=y

Export Utility

There are two methods for exporting table data:
1. Conventional path Export
2. Direct path Export

To use direct path Export, specify DIRECT=Y parameter on the command line or in the parameter file. The default is DIRECT=N, which extracts the table data using the conventional path.

The Export utility provides four modes of export:

    * Full
    * User (Owner)
    * Table
    * Tablespace

Full Database Export
- This mode exports the entire database.
- Specify FULL=y to export in full database mode
- Need to have EXP_FULL_DATABASE role to export in this mode.

Tablespace Level
- TABLESPACES parameter specifies that all tables in the specified tablespace to be exported in the dump file.
- This includes all tables contained in the list of tablespaces
- Indexes are exported with their tables, regardless of where the index is stored

User level
- User-mode exports can be used to back up one or more database users
- multiple users can be listed

- Example 1: To export complete scott Schema
  $exp scott/tiger@db_alias file=scott.dmp log=scott.log compress=y
- Example 2: To export the data using the parameter file 
  $exp userid=scott/tiger@orcl parfile=export.txt

  Where the parameter file export.txt contains

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

Table level
- All users can export in table mode and user mode.
- Table-mode exports can be used to back up one or more database tables
- multiple tables can be listed using TABLES parameter

- Example 1 : To export emp and dept table in scott schema: 
  $exp scott/tiger@db_alias file=emp.dmp tables=(emp,dept) log=emp.elog
- Example 2 : To export the subset of the table data in the scott schema, emp table 
  $exp scott/tiger tables=emp query="where deptno=10" log=emp.elog

Import Utility

- Example : 
  $imp scott/tiger file=emp.dmp full=y log=emp.ilog