Oracle Data Pump EXPDP IMPDP REMAP_DATA Parameter for Data Transformation with Example

advertisements

_____________________________________________________________________________________________________________________

EXPDP REMAP_DATA parameter is used to transform the column data to some other value using the database packages. Using this parameter you can translate or transform the critical informations like address, credit card number to some other values taking the export itself. This parameter can be used with import (IMPDP) utility also.

Using REMAP_DATA option data can be manipulated during the export and import job.

This feature can be used for protecting sensitive information during the export and import job.

Please find one example of transforming address to some other string during the export job.

Create a table say candidate

SQL> create table candidate(name varchar2(10), address varchar2(50));

Table created.

SQL> insert into candidate values ( 'James','Room no 1, 2nd main, block 4, 3 bldg');

1 row created.

SQL> insert into candidate values ( 'Scott','45/2, Flat 2b, Garden Appt,');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from candidate;

NAME ADDRESS

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

James Room no 1, 2nd main, block 4, 3 bldg

Scott 45/2, Flat 2b, Garden Appt,

Create package with a function to perform the string translation

create or replace package pkg_dat_remap as

function hide_address (add varchar2) return varchar2;

end;

create or replace package body pkg_dat_remap as

function hide_address (add varchar2) return varchar2

transform_address varchar2(50);

begin

SELECT TRANSLATE(add,'abcdefghijklmnopqrstuvxyz1234567890', 'zxcvbasdfqwertyu12345678901mlpoknbjiu') into transform_address from dual;

return transform_address;

end;

SQL> create or replace package pkg_dat_remap as

function hide_address (add varchar2) return varchar2;

end;/

Package created.

SQL> create or replace package body pkg_dat_remap as

2 function hide_address (add varchar2) return varchar2

3 as

transform_address varchar2(50);

4 5 begin

6 SELECT TRANSLATE(add,'abcdefghijklmnopqrstuvxyz1234567890', 'zxcvbasdfqwertyu12345678901mlpoknbjiu') into transform_address from dual;

7 return transform_address;

8 end;

9 end;

10 /

Package body created.

Perform the export using remap_data parameter

$ expdp directory=SOU_DIR_OBJ dumpfile=scott.candidate.dmp logfile=candidate.log tables=scott.candidate remap_data=scott.candidate.address:scott.pkg_dat_remap.hide_address

Export: Release 11.2.0.2.0 - Production on Wed Jan 11 04:00:13 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA directory=SOU_DIR_OBJ dumpfile=scott.candidate.dmp logfile=candidate.log tables=scott.candidate remap_data=scott.candidate.address:scott.pkg_dat_remap.hide_address

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 "SCOTT"."CANDIDATE" 5.492 KB 2 rows

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

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

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

/tmp/scott.candidate.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 04:00:24