Remap data in datapump

Remap data lets you export the data and modify the data based on a remapping scheme. The remap_data parameter uses a user defined package/function to alter the data.

DEMO:-

Create user "gg"

SQL> create user gg identified by gg;

User created.

SQL> grant dba to gg;

Grant succeeded.

SQL> conn gg/gg@delhi

Connected.

Create table "dp_test" in "gg"

SQL> create table dp_test (name varchar2(10),age number);

Table created.

SQL> insert into dp_test values('&a',&25);

Enter value for a: a

Enter value for 25: 10

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('a',10)

1 row created.

SQL> /

Enter value for a: b

Enter value for 25: 15

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('b',15)

1 row created.

SQL> /

Enter value for a: c

Enter value for 25: 22

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('c',22)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dp_test;

NAME              AGE

---------- ----------

a                  10

b                  15

c                  22

Create package: -

SQL>  create or replace package add_age as

  2   function modify_age (roll_in number) return number;

  3   end;

  4  /

Package created.

SQL>  create or replace package body gg.add_age as

  2   function modify_age (roll_in number) return number

  3   as

  4   roll_out number;

  5   begin

  6   roll_out := roll_in + 10;

  7   return(roll_out);

  8   end;

  9   end;

 10

 11  /

Package body created.

Create another user called "scott1".

SQL> create user scott1 identified by tiger1;

User created.

SQL> grant dba to scott1;

Grant succeeded.

Take export of table "dp_test".

SQL> host expdp gg/gg@delhi dumpfile=dp_test.dmp logfile=dp_test.log tables=gg.dp_test remap_data=gg.dp_test.age:gg.add_age.modify_age

Export: Release 11.2.0.1.0 - Production on Wed Sep 28 16:19:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "GG"."SYS_EXPORT_TABLE_01":  gg/********@delhi dumpfile=dp_test.dmp logfile=dp_test.log tables=gg.dp_test remap_data=gg.dp_test.age:gg.add_age.modify_age

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "GG"."DP_TEST"                              5.429 KB       3 rows

Master table "GG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for GG.SYS_EXPORT_TABLE_01 is:

  D:\APP\BISHWANATH\ADMIN\DELHI\DPDUMP\DP_TEST.DMP

Job "GG"."SYS_EXPORT_TABLE_01" successfully completed at 16:19:58

Now, import table "dp_test" in scott1 schema.

SQL> host impdp scott1/tiger1  dumpfile=dp_test.dmp logfile=dp_testimp.log remap_schema=gg:scott1 

Import: Release 11.2.0.1.0 - Production on Wed Sep 28 16:21:33 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT1"."SYS_IMPORT_FULL_01":  scott1/******** dumpfile=dp_test.dmp logfile=dp_testimp.log remap_schema=gg:scott1 table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT1"."DP_TEST"                          5.429 KB       3 rows

Job "SCOTT1"."SYS_IMPORT_FULL_01" successfully completed at 16:21:37

SQL> conn scott1/tiger1

Connected.

Check the data of "dp_test"

SQL> select * from dp_test;

NAME              AGE

---------- ----------

a                  20

b                  25

c                  32

As you see values in column "AGE" is incremented by 10.

Enjoy:-)