Oracle database 10g offers the new Data
pump feature, a server-side
infrastructure for fast data movement between Oracle databases. The data pump
feature enables DBAs to transfer large amounts of data and metadata at very
high speeds compared with the older export/import technology.
Oracle Data Pump feature enables very
high-speed movement of data and metadata from one database to another. This
feature is the basis for Oracle’s new data movement utilities, Data Pump Export
and Data Pump Import.
Data Pump enables you to specify whether a
job should move a subset of data and metadata. This is done using data filters
and metadata filters, which are implemented through Export and Import
parameters.
Data Pump Export is a utility for unloading
data and metadata into a set of operating system files called a dump file set.
The dump file set can be moved to another system and loaded by the Data Pump
Import utility.
The dump file set is made up of one or more
disk files that contain table data, database object metadata, and control
information. The files are written in a proprietary, binary format, which can
be read only by Data Pump Import. During an import operation, the Data Pump Import
utility uses these files to locate each database object in the dump file set.
Data Pump import is a utility for loading
an export dump file set into a target system. The dump file set is made up of
one or more disk files that contain table data, database object metadata.
Import can also be used to load a target
database directly from a source database with no intervening files, which
allows export and import operations to run concurrently, minimizing total
elapsed time. This is known as network import.
We need to perform the following steps to
use export Data Pump and Import Datapump:à
Allocate memory for STREAMS_POOL_SIZE using
the following command:
Sql>alter system set
STREAMS_POOL_SIZE=10M scope=both;
Create directory for storing dump files:
Mkdir directory name.
Sql>create directory dir_1 as
‘/data/oracle/datapump’;
To grant users access to these directories:
Sql>grant read, write on directory dir_1
to scott;
To export full database:
The user should have the following
privileges:
Exp_full_database
Imp_full_database
Sql>grant exp_full_database, imp_full_database to
scott;
To export:
$expdp scott/tiger directory=dir_1
dumpfile=1.dmp logfile=1.log full=y
To import:
$impdp scott/tiger directory=dir_1
dumpfile=1.dmp logfile=1.log full=y
To export full schema:
$expdp scott/tiger directory=dir_1
dumpfile=1.dmp schemas=scott
$expdp system/manager directory=dir_1 dumpfile=scott.dmp
schemas=scott
To import full schema:
$impdp scott/tiger directory=dir_1
dumpfile=1.dmp schemas=scott
$impdp system/manager directory=dir_1
dumpfile=scott.dmp schemas=scott
To export a table:
$expdp scott/tiger directory=dir_1
dumpfile=emp.dmp tables=emp
$expdp system/manager dumpfile=emp.dmp
tables=scott.emp
To import a table:
$impdp scott/tiger directory=db_1
dumpfile=emp.dmp tables=emp
$impdp system/manager dumpfile=emp.dmp
tables=scott.emp
In 9i/10g database without datapump utility:
$exp system/manager file=scott.dmp
log=scott.log owner=scott
$exp system/manager file=emp.dmp
log=emp.log tables=scott.emp
$imp scott/tiger file=emp1.dmp
fromuser=scott touser=scott