exp and imp are
utilities present in the $ORACLE_HOME/bin directory and are
installed when Oracle is installed. Their prime purpose is to move logical
objects out of and into the database respectively – for example dumping all of
the tables owned by a user to a single file is achieved using the exp utility.
exp and imp are
the executables that allow to make exports and imports of data objects (such
as tables). Therefore, logical backups can be made with exp.
exp/imp
allow to transfer the data accross databases that reside on different hardware
platforms and/or on different Oracle versions. If the data is exported on a
system with a different Oracle version then on that on which it is imported,
imp must be the newer version. That means, if something needs to be exported
from 10g into 9i, it must be exported with 9i’s exp.
imp
doesn’t re-create an already existing table. It either errors out or ignores
the errors.
In
order to use exp and imp, the catexp.sql script must be
run. catexp.sql basically creates the exp_full_database and imp_full_database roles.
It
is found under $ORACLE_HOME/rdbms/admin:
SQL>
@?/rdbms/admin/catexp
The
Export and Import tools support four modes of operation:
§
FULL : Exports all the objects in all schemas
§
OWNER : Exports objects only belonging to the given OWNER
§
TABLES : Exports Individual Tables
§
TABLESPACE : Export all objects located in a given TABLESPACE.
EXPORT
& IMPORT are used for the following tasks:
§
Backup ORACLE data in operating system files
§
Restore tables that where dropped
§
Save space or reduce fragmentation in the database
§
Move data from one owner to another
Exporting
of ORACLE database objects is controlled by parameters. To get familiar with
EXPORT parameters type:
exp help=y
You will get a short description and the default settings will be shown
exp help=y
You will get a short description and the default settings will be shown
Example
of Exporting Full Database
$exp USERID=scott/tiger
FULL=y FILE=myfull.dmp
In
the above command, FILE option specifies the name of the dump
file, FULL option specifies that you want to export the full
database, USERID option specifies the user account to connect to the
database.
Note: To
perform full export the user should have DBA or EXP_FULL_DATABASE privilege.
$exp USERID=scott/tiger
OWNER=(SCOTT,ALI) FILE=exp_own.dmp
The
above command will export all the objects stored in SCOTT and ALI’s schema.
$exp USERID=scott/tiger
TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp
This
will export scott’s emp and sales tables.
If
you include CONSISTENT=Y option in export command argument
then, Export utility will export a consistent image of the table i.e. the
changes which are done to the table during export operation will not be
exported.
Using
imp/exp across different Oracle versions
If
exp and imp are used to export data from an Oracle database with a different
version than the database in which is imported, then the following rules apply:
1.
Exp must be of the lower version
2.
Imp must match the target version.
IMPORT Utility
Commands:
Like
EXPORT the IMPORT utility is controlled by parameters. To get familiar with
these parameters type: imp help=y
You will get a short description of usage and default settings of parameters.
To start IMPORT simply type imp. You will be prompted for your ORACLE userid, password. The next prompts depend on what you answer.
You will get a short description of usage and default settings of parameters.
To start IMPORT simply type imp. You will be prompted for your ORACLE userid, password. The next prompts depend on what you answer.
$imp scott/tiger
FILE=myfullexp.dmp FROMUSER=scott TABLES=(emp,dept)
This
command will import only emp, dept tables into Scott
user and you will get a output similar to as shown below
Export
file created by EXPORT:V10.00.00 via conventional path
import done
in WE8DEC character set and AL16UTF16 NCHAR character set
. importing SCOTT’s
objects into SCOTT
.
. importing table “DEPT” 4 rows imported
.
. importing table “EMP” 14 rows imported
Import
terminated successfully without warnings.
For
example, suppose Ali has exported tables into a dump file mytables.dmp.
Now Scott wants to import these tables. To achieve this Scott will give the
following import command
$imp scott/tiger FILE=mytables.dmp FROMUSER=ali TOUSER=scott
Then
import utility will give a warning that tables in the dump file
was exported by user Ali and not you and then proceed.
Suppose
you want to import all tables from a dump file whose name matches a particular
pattern. To do so, use “%” wild character
in TABLES option. For example, the following command will import all
tables whose names starts with alphabet “e” and those tables whose name
contains alphabet “d”
$imp scott/tiger
FILE=myfullexp.dmp FROMUSER=scott TABLES=(a%,%d%)
Migrating a
Database across platforms.
The
Export and Import utilities are the only method that Oracle supports
for moving an existing Oracle database from one hardware
platform to another. This includes moving between UNIX and NT systems and also
moving between two NT systems running on different platforms.
The
following steps present a general overview of how to move a database between
platforms.
1.
As a DBA user, issue the following SQL query to get the exact name of all
tablespaces. You will need this information later in the process.
SQL>
SELECT tablespace_name FROM dba_tablespaces;
2.
As a DBA user, perform a full export from the source database, for example:
$ exp system/manager
FULL=y FILE=myfullexp.dmp
1.
Move the dump file to the target database server. If you use FTP, be sure
to copy it in binary format (by entering binary at the FTP prompt) to
avoid file corruption.
2.
Create a database on the target server.
3.
Before importing the dump file, you must first create your tablespaces, using
the information obtained in Step 1. Otherwise, the import will create the
corresponding datafiles in the same file structure as at the source database,
which may not be compatible with the file structure on the target system.
4.
As a DBA user, perform a full import with the IGNORE parameter enabled:
>
imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp
Using IGNORE=y instructs Oracle to
ignore any creation errors during the import and permit the import to complete.
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment