Question: I am attempting and export/import of
an Oracle database using Oracle's exp/imp functionality. Do
you have any tips for using the export and import commands with the
Oracle database? I thought I had done a good export, but I am
having trouble accessing it.
Answer: Oracle Export and Import using exp
and imp are powerful tools. Oracle now refers to these as
their "original" Export and Import utilities. Starting with
Oracle 10g, the Export and Import utilities are part of the
Data Pump utilities and are their preferred method of
Export and Import.
For previous Oracle versions and in certain circumstances for
Oracle 10g and beyond, it is still desirable to use the original exp
and imp commands to transfer data objects between databases.
The following special circumstances warrant the use of the original
exp and imp utilities even with Oracle 10g and higher:
- Importing from files originally created with the exp Export
utility.
- Exporting files to be imported using the original imp Import
utility.
The second case is particularly useful if the goal is to export
data from an Oracle 10g or higher database to be imported into an
older version of Oracle.
Using the exp command against an Oracle database extracts tables
followed by any related objects such as indexes, comments or grants,
if any exist. The data is written to an export dump file which
can be transferred to another virtual or physical location.
Once the file is transferred to the appropriate location, the imp
Import utility can then be used to read the object definitions and
table data from the dump file created by the exp Export utility.
This allows data to be transferred between databases that are remote
from each other or are not connected via a network. The files
can also be used as backups as an auxiliary to standard backup
procedures.
It is important to note that the export dump files can only be
read by the import utility, and the version of the export utility
used to create the dump file cannot be newer than the import utility
to be used to read the file.
There are some things you need to do before you get started on
your Oracle Export and Import. Failure to accomplish these
tasks may cause your Oracle Export and Import to fail:
- Verify that there is sufficient disk or tape storage to
write the export file. If the export runs out of space, it
will terminate with an error on write failure.
- Verify that you have adequate access privileges to perform
the export. Generally, database administrators have
sufficient privileges to accomplish a full export. Users
without sufficient privileges are not allowed to export tables
owned by another user.
imp does not require that the same user perform the import;
however, it is important that the user have at least the same role
or greater privileges granted as the user who performed the export.
Again, database administrators generally have sufficient privileges
to execute imports.
If you are having problems accessing the export, it could be an
issue with privileges.
The following sources of information may be useful to you:
- For more information on Oracle Export and Import as well as
other Oracle Utilities, also check out the
Advanced Oracle Utilities book by Scalzo, Burleson and
Callan.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
