Wednesday, September 28, 2011

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:-)