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. TaniMay 20, 2012 at 5:47 AM

    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

What is Oracle Integration Cloud Service - ICS?

Image
Oracle Integration Cloud Service (ICS) is a solution that enables you to simplify integrations between cloud applications, and between cloud and on premises applications. It helps you create connections to well-known and less-known SaaS and PaaS applications, using the available cloud adapters, publish or subscribe to the Messaging Cloud Service, or use industry standards such as SOAP, REST, FTP, File, and JMS. For more information watch this Oracle video.
Read more

How to Create Packages in Oracle Database using TOAD for Oracle

Image
What are Packages in Oracle Database A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body. Package Specification Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Package Body Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.
Read more

How to create a Simple Scheduler Job in Oracle Database using Toad

Image
Scheduler is a process which runs programmed jobs at scheduled times. This process wakes up at the specified time and runs automatically. The basic elements of the scheduler are: Jobs Schedules Programs A job specifies what needs to be executed and when. A program is a collection of metadata that will be executed by the scheduler. It contains the name of the program (a procedure or an executable, the type of the program (pl/sql block, shell script etc). A schedule specifies when and how many times a job is executed. 1. Login to the database using Toad, and create a Scheduler Job as follows;
Read more