Use of Import & Export Utility in Oracle Database


Oracle export and import 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.
 
The export/import utilities are commonly used to perform the following tasks:

1) Backup and recovery (small databases only)
2) Move data between Oracle databases on different platforms (for example from Solaris to Windows)
3) Reorganization of data/eliminate database fragmentation (export, drop and re-import tables)
4) Upgrade databases from extremely old versions of Oracle
5) Detect database corruption. Ensure that all the data can be read
6) Transporting tablespaces between databases 

Given Below is the example of Export Data from Prod Database and Import to ORCL Database

Screen Shots of Export Data:

1) 
2)

Create User in ORCL Database 

User Creation Script:-

DROP USER EXPIMPUsr CASCADE;
CREATE USER EXPIMPUsr
  IDENTIFIED BY mw6
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
   GRANT CONNECT TO EXPIMPUsr;
  ALTER USER EXPIMPUsr DEFAULT ROLE ALL;
  GRANT UNLIMITED TABLESPACE TO EXPIMPUsr;
  GRANT CREATE SESSION TO EXPIMPUsr;
  GRANT CONNECT TO EXPIMPUsr;
  GRANT EXPORT FULL DATABASE TO EXPIMPUsr;
  GRANT IMPORT FULL DATABASE TO EXPIMPUsr;
  GRANT RESOURCE TO EXPIMPUsr;
  GRANT CREATE PROCEDURE TO EXPIMPUsr;

Screen Shots of Import Data:

1) 
 2)
3)

4)

Import Done in ORCL.

Comments

  1. Interesting to learn how Oracle export and import utilities are used to perform logical database backup and recovery. The utilities can be used to move data between different machines, databases or schema.

    sap erp training

    ReplyDelete

Post a Comment

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle