Data Pump continued … Remap_data

Remap data (remap_data) is new feature that’s introduced in Oracle 11g data pump tool. Thie feature lets your 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.

Syntax

REMAP_DATA=[(chema.tablename.column_name:schema.pkg.function)

Example

Look at table scott.dp_test

SQL> desc scott.dp_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(10)
AGE NUMBER

SQL> select * from scott.dp_test;

NAME AGE
---------- ----------
ahmad 25
Gairik 31
Ryan 18
Greg 29

SQL>

I need to export the data and increment every ones age by 10.The package has a function named modify_age which accepts age as input and add 10 to it.

SQL> ed
Wrote file afiedt.buf

1 create or replace package add_age as
2 function modify_age (roll_in number) return number;
3* end;
SQL> /

Package created.

SQL> create or replace package body scott.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 /

Package body created.

I am planning to import the table to scott1 schema. I have created a new user named Scott1 and created the table named dp_test similar to scott.dp_test.

SQL> create user scott1 identified by xxxxxxxx;

User created.

SQL> grant create session, imp_full_database to scott1;

Grant succeeded.

SQL> create table scott1.dp_test as select * from scott.dp_test;

Table created.

SQL> delete from scott1.dp_test;

4 rows deleted.

SQL> commit;

Commit complete.

Granting read/write privs on exp_dir where the dump file resides.

SQL> grant read, write on directory exp_dir to scott1;

Grant succeeded.

Export the data from scott.dp_test using the remap_data function which was created above.

H:\>expdp scott/xxxxxxx directory=exp_dir dumpfile=exp_dir:dp_test.dmp logfile=exp_dir:dp_test.log tables=scott.dP_test remap_data=scott.dp_test.age:scott.add_age.modify_age
Export: Release 11.1.0.6.0 - 64bit Production on Tuesday, 14 April, 2009
16:13:08

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=exp_dir dumpfile=exp_dir:dp_test.dmp logfile=exp_dir:dp_test.log table
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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DP_TEST" 5.453 KB 4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
D:\EXP_DIR\DP_TEST.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 16:13:26

Export completed successfully

I am importing the dump file to scott1.dp_test.

H:\>impdp scott1/xxxxxxxx directory=exp_dir dumpfile=exp_dir:dp_test.dmp logfile=exp_dir:dp_testimp.log remap_schema=scott:scott1 table_exists_action=append

Import: Release 11.1.0.6.0 - 64bit Production on Wednesday, 15 April, 2009 11:24:46

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit 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/******** directory=exp_dir dumpfile=exp_dir:dp_test.dmp logfile=exp_dir:dp_testimp.log remap
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SCOTT1"."DP_TEST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT1"."DP_TEST" 5.453 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT1"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:24:48

Verify the Result

Lets query the data on scott1.dp_test and see if every ones age is incremented by 10

SQL> select * from scott1.dp_test;

NAME AGE
---------- ----------
ahmad 35
Gairik 41
Ryan 28
Greg 39

SQL>

Similarly you can write your functions to map the data the way you wanted.

Conclusion:

Datapump is a great tool for DBA’s and developers. Oracle 11g has made the tool more powerful. The more you use it, the more you love it .