As we all know Datapump is the Oracle preferred tool
for moving data and is soon will be the only option because traditional. exp/imp utilities will be deprecated. In following
sections we will look at how you can use schema, table and data remapping to
get more out of this powerful utility.Data remapping allows you to manipulate
sensitive data before actually placing the data inside the dump file. This can
happen on done at different stages including during schema remap, table remap
and remapping
of individual rows inside tables i.e. data
remapping. We will look at them one by one in this section.
First setp -
Setup
both your source and target database:
1.
sqlplus to source and target DB as sys or system.
2.
create directory with the following command:
CREATE
DIRECTORY dpump_dir1 AS '/backup/folder complte location';
Select the table DBA_DIRECTORIES will give you
the list of all the directories that you have created using CREATE DIRECTORY
command.
Exit
sqlplus.
4. Run the following command to export using data
pump:
expdp system/[PASSWORD]@[SID] schemas=[SCHEMA]
DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=[SCHEMA]_[SID]_%u.dmp PARALLEL=4
– replace strings in [ ] with the ones for your
environment.
5. Run the following command to import using data
pump:
impdp system/[PASSWORD]@[SID] schemas=[SCHEMA]
DIRECTORY=dpump_dir1 JOB_NAME=hr DUMPFILE=[SCHEMA]_[SID]_%u.dmp PARALLEL=8
– replace strings in [ ] with the ones for your
environment.
Notes:
- If your target database is on another host, be
aware of the directory setup.
- If you want to import into existing table, simply
add TABLE_EXISTS_ACTION=APPEND as part of the command.
- If you only want to import certain tables, use
TABLES=[TABLE_NAME1,TABLE_NAME2 ...etc].
- You can also use TABLE_EXISTS_ACTION=TRUNCATE to
first truncate the target table before the import.
- You can adjust PARALLEL parameter depending on the
number of CPUs on your system.
Schema Remapping
When you export a schema or some objects of a schema
from one database and import it to the other then
import utility expects the same schema to be present
in second database. For example if you export EMP table of SCOTT schema
and import it to another then import utility will
try to locate the SCOTT schema in second database and if not present,
it may create it for you depending on the options
you specified.
But if you want to create the EMP table in SH schema
instead. The remap_schema option of impdp utility will allow you to accomplish
that.
For example
$ impdp userid=rman/rman@orcl
dumpfile=data_pump:SCOTT.dmp remap_schema=SCOTT:SH
Table Remapping
On similar grounds you can also import data from one
table into a table with a different name by using
the REMAP_TABLE option. If you want to import data
of EMP table to EMPTEST table then you just have to provide the
REMAP_TABLE
option with the new table name.
This option can be used for both partitioned and
nonpartitioned tables.
On the other side however table remapping has the
following restrictions.
If
partitioned tables were exported in a transportable mode then each partition or
subpartition will be moved to a separate table of its own.
Tables
will not be remapped if they already exist even if you specify the
TABLE_EXIST_ACTION to truncate or append.
The export must be performed in non
transportable mode.
The syntax of REMAP_TABLE is as follows:
REMAP_TABLE=[old_schema_name.old_table_name]:[new_schema_name.new_table_name]
