“exp” and “imp” are
the executables used to make exports and imports of data objects.
When you want to export
tables from source database, this “exp” executable will back up the database
objects into a binary file at OS level, called the dump file. This dump file needs
to be transferred from source Server to the target Server. At the target
database, “imp” executable will copy the data from the dump file to the
target database.
When an export command is
executed on a database, database objects are extracted with their dependent
objects i.e., if it backups a table, its dependencies like indexes, comments,
and grants, etc. are extracted and written into the dump file.
Logical backup and recovery can be made with “exp”
and “imp” utilities respectively.
Datapump utilities:
From Oracle 10g onwards, a new utility was introduced
for export and import – expdp and impdp.
Oracle Data Pump is a
newer, faster and more flexible alternative to the "exp" and
"imp" utilities used in previous Oracle versions. In addition to
basic import and export functionality data pump provides a PL/SQL API and
support for external tables.
Pre-requisite:
The user should have
assigned with EXP_FULL_DATABASE & IMP_FULL_DATABASE roles.
catexp.sql
(in $ORACLE_HOME/rdbms/admin) is in-charge of creating EXP_FULL_DATABASE &
IMP_FULL_DATABASE roles in the database. This script is in-built in
“catalog.sql”. So, if “catalog.sql” script was executed at the time of database
creation, then it is not needed to run “catexp.sql” explicitly.
The catexp.sql script
performs the following tasks to prepare the database for export and import
operations:
- Creates the necessary export and
import views in the data dictionary
- Creates and
assigns all necessary privileges to EXP_FULL_DATABASE and IMP_FULL_DATABASE
roles
- Assigns EXP_FULL_DATABASE and IMP_FULL_DATABASE to
the DBA role
- Records the version of catexp.sql that
has been installed
Four modes of operation:
The Export and Import
utilities support four modes of operation:
Ø
Full:
-
Exports and imports a full database.
-
Only users with the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles
can use this mode.
-
Achieved by using “FULL=y” parameter to
specify this mode.
Ø
Tablespace:
-
Exports and imports a specified set of
tablespaces from one Oracle database to another.
-
Achieved by using “TRANSPORT_TABLESPACE” parameter
to specify this mode.
Ø
User:
- A privileged user can import all objects (such
as tables, grants, indexes, and procedures) in the schemas of a specified set
of users.
- Achieved by using “OWNER” parameter
to specify this mode in Export and "FROMUSER" parameter to specify
this mode in Import.
Ø
Table:
- A privileged user can export and import
specific tables and partitions by mentioning the schema that contains the
tables.
- If a schema name is not specified for any
table, then Export uses the exporter's schema name.
- Achieved by using “TABLES” parameter
to specify this mode.
Points to remember:
@ Export
dump files can be read only by the Oracle Import utility
@ The
version of the Import utility should not be earlier than the version of the
Export utility used to create the dump file.
@ A
full export does not export triggers owned by schema SYS. SYS triggers are
to be manually re-created either before or after the full import. Oracle
recommends to re-create them after the import in case they define actions that
would impede progress of the import.
@ A
full export also does not export the default profile. If you have modified the
default profile in the source database (for example, by adding a password
verification function owned by schema SYS), then you must manually re-create
the function and modify the default profile in the target database after the
import completes.
@ When you perform
the import, ensure you are pointing to the correct instance. This is very important
because on some UNIX systems, just the act of entering a sub-shell may change
the database name against which an import operation is to be performed.
No comments:
Post a Comment