Oracle preferred tool for moving data

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]