The Oracle database provides built-in utilities for exporting and importing data. These utilities are essential for tasks such as backups, migrations, and data sharing. There are two primary sets of tools: the traditional `exp`/`imp` commands and the more modern `expdp`/`impdp` Data Pump utilities.

exp and imp commands are compatible with all Oracle versions and are suitable for smaller datasets or when Data Pump is not available.

exp - Oracle Export Utility

The exp command is used to extract data from an Oracle database into a binary dump file.

Syntax:

exp userid=username/password@host:port/service_name
    file='output_file_path.dmp'
    log='log_file_path.log'
    full=[y|n]
    tables='table1,table2,...'
    owner='schema_name'
    indexes=[y|n]

• userid: The database user credentials for connecting to the database (e.g., system).
• file: Specifies the path for the output dump file. This directory must be accessible by the Oracle user.
• log: Specifies the path for the export log file.
• full: If set to 'y', performs a full database export.
• tables: Specifies the tables to be exported.
• owner: Specifies the schema (database user) whose objects are to be exported.
• indexes: If 'y', exports indexes; if 'n', excludes indexes. Defaults to exporting indexes.

Example: Exporting a Schema (User)

exp system/"your_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/scott_schema.dmp'
    log='/u01/app/oracle/dmp/scott_schema.log'
    owner=scott
    indexes=n

This command exports the schema named 'scott' without its indexes. Tables without data will not be included in the export if using the owner clause this way.

Example: Exporting Specific Tables

exp scott/"scott_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/scott_tables.dmp'
    log='/u01/app/oracle/dmp/scott_tables.log'
    tables=scott.dept,scott.emp
    indexes=n

This command exports only the 'dept' and 'emp' tables from the 'scott' schema.

Example: Full Database Export

exp system/"your_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/full_db.dmp'
    log='/u01/app/oracle/dmp/full_db.log'
    full=y
    indexes=n

This command exports the entire Oracle database.

imp - Oracle Import Utility

The imp command is used to load data from a dump file created by exp back into an Oracle database.

Syntax:

imp userid=username/password@host:port/service_name
    file='dump_file_path.dmp'
    log='log_file_path.log'
    fromuser='source_schema'
    touser='destination_schema'
    tables='table1,table2,...'
    owner='schema_name'
    full=[y|n]

Example: Importing a Schema to a New User

First, create a new user and tablespace:

SQL> CREATE TABLESPACE new_data datafile '/u01/oradata/new_data.dbf' SIZE 1G AUTOEXTEND ON;
SQL> CREATE TEMPORARY TABLESPACE new_temp tempfile '/u01/oradata/new_temp.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
SQL> CREATE USER new_user IDENTIFIED BY new_password DEFAULT TABLESPACE new_data TEMPORARY TABLESPACE new_temp;
SQL> GRANT CONNECT, RESOURCE TO new_user;

Then, import the data:

imp system/"your_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/scott_schema.dmp'
    log='/u01/app/oracle/dmp/import_scott.log'
    fromuser=scott
    touser=new_user

After import, connect as new_user to verify:

SQL> CONNECT new_user/new_password
SQL> SELECT table_name FROM user_tables;

Example: Importing Specific Tables

-- Ensure tables are dropped if re-importing into the same schema
-- SQL> DROP TABLE dept;
-- SQL> DROP TABLE emp;

imp system/"your_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/scott_tables.dmp'
    log='/u01/app/oracle/dmp/import_tables.log'
    fromuser=scott
    touser=scott
    tables=dept,emp

Example: Full Database Import

imp system/"your_password"@192.168.1.100:1521/ORCL
    file='/u01/app/oracle/dmp/full_db.dmp'
    log='/u01/app/oracle/dmp/import_full.log'
    full=y

The expdp and impdp utilities, introduced in Oracle 10g, are known as Data Pump. They offer significant performance improvements, especially for large datasets, and provide more advanced features.

expdp - Data Pump Export Utility

expdp exports data using Oracle Directory objects, which are database objects that map to physical file system directories.

Step 1: Create a Directory Object

First, ensure the directory exists on the database server and then create a corresponding Oracle Directory object:

-- On the database server:
-- $ mkdir /opt/oracle/dmpfiles

-- Connect to SQL*Plus as SYSDBA:
SQL> CREATE DIRECTORY DMP_DIR AS '/opt/oracle/dmpfiles';
SQL> GRANT READ, WRITE ON DIRECTORY DMP_DIR TO system; -- Or the user performing the export

Syntax:

expdp system/"your_password"@host:port/service_name
    directory=DIRECTORY_OBJECT_NAME
    dumpfile=dump_file_name.dmp
    logfile=log_file_name.log
    schemas=schema1,schema2,...
    tables=schema.table1,schema.table2,...
    full=[y|n]

Example: Exporting a Schema

expdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_scott_schema.dmp
    logfile=expdp_scott_schema.log
    schemas=scott

This exports the 'scott' schema. Even empty tables will have their structure exported.

Example: Exporting Specific Tables

expdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_scott_tables.dmp
    logfile=expdp_scott_tables.log
    tables=scott.emp,scott.dept

Example: Full Database Export

expdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_full_db.dmp
    logfile=expdp_full_db.log
    full=y

impdp - Data Pump Import Utility

impdp is used to import data from Data Pump dump files.

Syntax:

impdp system/"your_password"@host:port/service_name
    directory=DIRECTORY_OBJECT_NAME
    dumpfile=dump_file_name.dmp
    logfile=log_file_name.log
    tables=schema.table1,schema.table2,...
    remap_schema='source_schema:target_schema'
    full=[y|n]

Example: Importing a Schema to a Different Schema

Ensure the target schema ('bow' in this case) exists and has necessary privileges.

-- Connect as target user (bow) and verify no tables exist initially
-- SQL> CONNECT bow/bow_password
-- SQL> SELECT table_name FROM user_tables; (Should show no rows)

impdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_scott_schema.dmp
    logfile=impdp_scott_schema.log
    remap_schema=scott:bow

After import, connect as the target user (bow) to verify the tables:

SQL> CONNECT bow/bow_password
SQL> SELECT table_name FROM user_tables;

Example: Importing Specific Tables into a Schema

-- Ensure target schema (bow) exists and is ready.
-- You might want to drop existing tables first if re-importing.
-- SQL> CONNECT bow/bow_password
-- SQL> DROP TABLE emp;
-- SQL> DROP TABLE dept;

impdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_scott_tables.dmp
    logfile=impdp_scott_tables.log
    remap_schema=scott:bow
    tables=scott.emp,scott.dept

Connect as the target user (bow) to verify the imported tables.

Example: Full Database Import

impdp system/"your_password"@192.168.1.100:1521/ORCL
    directory=DMP_DIR
    dumpfile=expdp_full_db.dmp
    logfile=impdp_full_db.log
    full=y