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

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.

  1. 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,


  1. 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
as
  transform_address varchar2(50);
begin
  SELECT TRANSLATE(add,'abcdefghijklmnopqrstuvxyz1234567890', 'zxcvbasdfqwertyu12345678901mlpoknbjiu') into transform_address from dual;
  return transform_address;
end;
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.

  1. 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

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

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

  1. For test purpose drop the table candidate
SQL> drop table candidate;

Table dropped.

  1. Import the dump with full=y option

$ impdp directory=SOU_DIR_OBJ dumpfile=scott.candidate.dmp logfile=imp_candidate.log full=y

Import: Release 11.2.0.2.0 - Production on Wed Jan 11 04:02:44 2012

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

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
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=SOU_DIR_OBJ dumpfile=scott.candidate.dmp logfile=imp_candidate.log full=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."CANDIDATE"                       5.492 KB       2 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 04:02:51

  1. Check the values in table candidate it will be translated to some other values.

SQL> select * from candidate;

NAME       ADDRESS
---------- --------------------------------------------------
James      Ryyr ty 0, 1tv rzft, xeycw l, m xevs
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google