Export Import Utility

Who remembers the Export-Import definition from grade school? According to Investopedia, Export is a function of international trade whereby goods produced in one country are shipped to another country for sales or trade. An Import is a good or service brought into one country from another. The concept of export and import is similar in Oracle to that of import and export goods to one country. Oracle has a built-in Export-Import utility that lets anyone export Oracle objects, schema, database, and import back to the different or same database.

This utility is frequently used to move or copy tables, data, schemas, database, database objects from one database to another even if the target database is on the different platform, hardware and software configurations. As an application DBA, I use this to move table & data from DEV to UAT and to PROD environment.

To use Export Utility, you must have CREATE SESSION privilege on Oracle Database. To export tables owned by another user, you must have the EXP_FULL_DATABASE role enabled. This role is granted to all DBAs. To use Import Utility, you must have CREATE SESSION privilege on Oracle Database. If the export file was created by a user with EXP_FULL_DATABASE privilege, then you must have the IMP_FULL_DATABASE privilege to import it which is typically assigned to DBA role.

During export, the utility writes the dump file to your PC, not the server or the host where the database sits. This tool is more for database developer who does not have DBA or export-import roles. This utility also does not require access to the host machine. Oracle has another utility called data pump which is introduced on Oracle 10g this data pump is similar to export-import utility but much faster and efficient. The drawback is you must have access to a host where the export dump resides. Data pump is more for system DBA or sysadmin not designed for developers.

What to check before import? If importing tables and data, verify the table already exists. If a table exists, you can ignore to CREATE table and just load data. Other options would be to drop table and import back table structure and data. You may lose some table privilege with second options unless you want same privileges from the source, therefore, you are required to know all the options that export and import utility offers.

Table Export/Import on the Same Schema:

EXPORT:
        exp USERID=username/password@source_instance TABLES=(table_name) LOG=exp_table_name.log FILE=table_name.dmp
IMPORT:
        exp USERID=username/password@target_instance TABLES=(table_name) log=imp_table_name.log file=table_name.dmp

Example: Single table On the Same Schema
 exp USERID=hr/password@DEV TABLES=(employee) LOG=employee_table_DEV.log FILE=employee_table.dmp
 imp USERID=hr/password@PROD TABLES=(employee) LOG=employee_table_PROD.log FILE=employee_table.dmp
 
Example: Multiple tables On Same Schema:
 exp USERID=hr/password@DEV TABLES=(employee, dept) LOG=employee_dept_table_DEV.log FILE=employee_dept_table.dmp
 imp USERID=hr/password@PROD TABLES=(employee, dept) LOG=employee_dept_table_PROD.log FILE=employee_dept_table.dmp

Table Export/Import on the Different Schema:

EXPORT:
         exp USERID=username_1/password@source_instance TABLES=(table_name) LOG=table_name.log FILE=table_name.dmp
IMPORT: 
         imp USERID=username_2/password@target_instance TABLES=(table_name) fromuser=username_1 touser=username_2 LOG=table_name.log FILE=table_name.dmp
  
Example: Single table On Different Schema:
 exp USERID=hr/password@DEV TABLES=(employee) LOG=exp_employee_table_DEV.log FILE=employee_table.dmp
 imp USERID=dept/password@PROD TABLES=(employee) fromuser=hr touser=dept LOG=imp_employee_table_PROD.log FILE=employee_table.dmp
 
Example: Multiple tables On Different Schema:
 exp USERID=hr/password@DEV table(employee, dept) LOG=exp_employee_dept_table_DEV.log FILE=employee_dept_table.dmp
 imp USERID=dept/password@PROD table(employee, dept) fromuser=hr touser=dept LOG=imp_employee_dept_table_PROD.log FILE=employee_dept_table.dmp

Full Schema Export/Import:

Example: Full Export/Import on the Same Schema:
        exp USERID=hr/password@DEV owner=schema_name log=exp_owner_schema.log file=owner_schema.dmp
 imp USERID=hr/password@PROD fromuser=owner_schema log=imp_owner_schema.log file=owner_schema.dmp

Example: Full Schema Export/Import on The Different Schema:
 exp USERID=hr/password@DEV owner=schema_name log=exp_owner_schema.log file=owner_schema.dmp
 imp USERID=hr/password@PROD fromuser=owner_schema toschema=owner_schema log=imp_owner_schema.log file=owner_schema.dmp

Whole Database Export/Import:

Example:Full Database Export/Import:
 exp USERID=hr/password@DEV full=y log=exp_entire_db.log file=entire_db.dmp
 imp USERID=hr/password@PROD log=imp_entire_db.log file=entire_db.dmp

Help: 

imp help=y      (displays all import options)
exp help=y      (displays all export options)

Help displays all the options with a short description for export and import. This is the handy reference when writing export and import command. I refer help command all the time which saves time from going back to Oracle document for the options that I need.