How to import/export a Database in Oracle in windows?

Oracle is one of the most popular database. Normally, in our software life, we will face those situation where we have to switch our databases.

Here, I am trying to focus on oracle import/export functionality using commands by which you can migrate your database from oracle to others and vice versa. Also those tips also be used for taking up backup.

Things You'll Need

Basic knowledge of database and SQL
Oracle installed in your system
Valid database user name and password with enough import/export privileges.

Steps

1

Import
Execute the CATALOG.SQL Script
This script is ONLY EXECTUED ONCE. After executing the script once, you will then be able to export as often as you want.
Get a DOS command prompt.

Go to the following directory, where you install the orcale rdbms. for example

cd c:\oracle\ora81\rdbms\admin

Get into sqlplus using the “SYS” user:

sqlplus sys/change_on_install@cs325

Execute the catalog.sql database script:

@catalog.sql

Wait for about 5 minutes for the script to complete.

2

Export
Step 1: Execute the CATALOG.SQL Script on the database you are going to import to. [ Follow the Step-1 from Import section]

Step 2: Run the export command pressing enter at each prompt to select defaults.

Note the userid/password I’m providing in this example may not be the userid and password you want to use:

exp scott/tiger@cs325

OR

exp sys@repc_brd as sys dba/sys

You should verify at the end of the export that the tables, which are created by you for your project are actually being exported, by observing the “exporting table” information that appears in the export log.

In this example, there were four tables exported: BONUS, DEPT, EMP, and SALGRADE.ss

Tips

You can also use oracle administrator panel to export/import the database.

Caution

Mention user name and password are the samples.
Above command are subject to change with version.
No guarantee for your data

The How-To site for Indians World-wide

categories