How to SCRUB/MASK data using expdp (REMAP_DATA feature)

This is a common requirement for DBA to export data from production for various purposes. In a restricted & compliant environment it’s a must to mask / scrub particular data while exporting from production.

Here’s a simple demonstration. I’ve got the hint from metalink.

  1. Create a package to mask data

Note: The package needs to be created under the schema which would be used to connect to datapump utility. I’ve used SYSTEM user for this case.

create or replace package datapump_remap_test

as

  function toggle_case(p_value varchar2) return varchar2;

end;

/

create or replace package body datapump_remap_test

as

  function toggle_case(p_value varchar2) return varchar2 is

  begin

    return translate(p_value,    

      'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-=\/ ',

        'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

end;

end;

/

  2. Datapump export with REMAP_DATA parameter:

We’d be using the created package to scrub data for particular table columns. My package would basically replace all mentioned column data into ‘xxxxx’

For this example, I’ve chosen to scrub HR.FIRST_NAME & SALES. The export dump would export the FNADVI schema as well as the the table mentioned with scrubbed column.

expdp system dumpfile=fnadvi.dmp directory=dump logfile=fnadvi.dmp.log schemas=fnadvi remap_data=fnadvi.HR.first_name:system.datapump_remap_test.toggle_case remap_data=fnadvi.SALES.address:system.datapump_remap_test.toggle_case

  3. Import the schema

Now you the import can be done anywhere with scrubbed data.