1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Export and Import Utility

Discussion in 'SQL PL/SQL' started by SBH, Jan 3, 2011.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    Oracle provides Export and Import utilities for the transfer of data from one database to another. It enables writing of data i.e. Export of one database into Operating system files in binary format. These OS files in binary format can only be traced by IMPORT utility to move the data to the new owner.

    Efficiency of Export and Import Utilities
    1. Archive data for future reference and restore database object definitions
    2. Enable data movement between the databases which may be of different versions
    3. Transport tablespace(s) between databases

    Export/Import utility prove to be useful in situations of database corruptions and user errors. They can be used for logical database recovery. They can also be used for partial exporting of objects. User can take export and import few tables or specified tablespace.

    2. Export

    Export utility extract database objects along with their dependent and referenced objects. The export utility creates a binary format dump file at the specified location.

    Export Syntax
    Code (Text):
    exp username/password file=file.dmp log=log.txt statistics=none
    In the syntax,
    username/password is the source database username and password
    file.dmp is the name of the file where the data gets backed up.
    log.txt is the log file which contains relevant information about the export status

    3. Import

    The Import utility reads the object definitions and table data from an Export dump file. It inserts the data objects with or without data into an Oracle database.

    Import Syntax
    Code (Text):
    imp username/password fromuser=User1 touser=User2 file=samepage.dmp log=implog.txt
    In the syntax,
    Username/Password is the username and password of the target Oracle Database
    User1 is the original DB user from where the data was exported
    User2 is the new DB user into which the data is being imported
    file.dmp is the exported database dump file
    log.txt is file to store the import logs

    4. Utility Invoking methods

    Export and Import can be invoked using below methods.
    1. Command-line entries
    2. Interactive prompts
    3. Parameter files
    4. Oracle Enterprise Manager Data Management Wizard

    Since command line method is the most conventional and widely used method, we shall concentrate our discussion on it.

    5. User Requirements

    • User must possess the CREATE SESSION privilege on source or target Oracle database.
    • To export or import tables which owned by another user, user must possess the EXP_FULL_DATABASE and IMP_FULL_DATABASE role enabled.

    6. Export Parameters and Modes

    Below table shows the parameter which can be specified during EXPORT utility.
    [​IMG]

    Below are the modes of operation of EXPORT.
    [​IMG]

    7. Import Parameter and Modes

    Below table shows the parameter which can be specified during IMPORT utility.
    [​IMG]

    Below are the modes of operation of IMPORT.
    [​IMG]

    Order of database object Import
    Import utility maintains a sequence of object import to avoid redundancy of trigger firing and other considerable scenarios of referential integrity. This sequence is less preferential in case of Full Database Import.
    [​IMG]

    8. Examples

    1. Below command line EXPORT command takes export of EMPLOYEE and EXP_ARCHIVE table from CLUB schema. Note that it also includes rows(data) from the table

    Code (SQL):
    EXP CLUB/CLUB TABLES=EMPLOYEE,EMP_ARCHIVE ROWS=y file=tabexp.dmp
    2. Below EXPORT command takes export only for the tablespace TBS_ORDERS. Note that transportable tablespace can be exported or imported only by SYSDBA.

    Code (Text):
    exp \'username/password AS SYSDBA\' TRANSPORT_TABLESPACE=y TABLESPACES=tbs_orders file-exptbs.dmp log=exptbs.log
    3. The below command line EXPORT command imports the objects owned by CLUB schema into ORCL user of SYSTEM database using the export file EXPFULL.dmp

    Code (SQL):
    imp system/manager FROMUSER=CLUB TOUSER=ORCL file=EXPFULL.dmp
     

    Attached Files:

  2. usman1

    usman1 Guest

    a nice article on exp / imp utility... will however be more informative if u added the datapump technology while explaining exp and imp cmd structure.