# Remapping Data - Oracle 11g

Oracle Database 11g provides the REMAP_DATA parameter, which allows you to transform data during the export or import operation.To perform a data remap operation,you must create a stored package function and supply the package.function name to the REMAP_DATA parameter as a command-line option.

In the following example,the SALARY column data will be transformed to illustrate the data re mapping feature.The salary of every employee can be remapped to $35,000 before storing the data on disk.A simple package with a single function is provided to remap the salary column.The edba_remap function must be passed into the REMAP_DATA parameter of Data Pump. create or replace package edba_remap is function remap_sal (p_sal number) return number; end; / create or replace package body edba_remap is function remap_sal (p_sal number) return number as v_sal number := 35000; begin return v_sal; end; end; / SQL> Package created. SQL> Package body created. We now have a package function to remap our SALARY column to the value$35,000. Let’s export the EMP table and remap the salary information.The REMAP_DATA parameter accepts the schema name.table_name.column_name value followed by a colon followed by a package.function_name value.In the following example, the SALARY column of the EMP table for the RODBA schema is being remapped to $35,000 during the export: expdp dumpfile=oratmp:emp.dmp tables=rodba.emp \ userid=rodba/oracle123 reuse_dumpfiles=y \ remap_data=rodba.emp.sal:edba_remap.remap_sal Once the export is complete and the salary information is remapped, let& rsquo;s import the data into another schema and confirm that the salary information did actually get changed: impdp tables=rodba.emp remap_schema=rodba:ckim \ userid=rodba/oracle123 dumpfile=oratmp:emp.dmp Here’s what the employee information looks like after the import is complete: 1* select ename, sal from emp SQL> / ENAME SAL ---------- ---------- WARD 35000 JONES 35000 MARTIN 35000 BLAKE 35000 CLARK 35000 SCOTT 35000 KING 35000 TURNER 35000 ADAMS 35000 JAMES 35000 FORD 35000 Sam 35000 Charles 35000 13 rows selected. The REMAP_DATA option worked as expected,and all the employee salaries are updated to$35,000.Other great use cases for the REMAP_DATA parameter of Data Pump include tasks such as changing Social Security infor mation,banking infor mation, credit card information,and so on.By generating random numbers using the DBMS_RANDOM package,you can dynamically change sensitive data at either export time or import time.For example,the remap_ssn function in the following edba_remap_ssn package replaces Social Security numbers with random,numerical values:

create or replace package edba_remap_ssn
is
function remap_ssn return number;
end;
/
create or replace package body edba_remap_ssn
is
function remap_ssn return number
IS
v_ssn NUMBER;
begin
SELECT dbms_random.value(100000000,999999999) into v_ssn FROM dual;
return v_ssn;
end;
end;
/

You can customize functions similar to the remap_ssn function to custom-tailor sensitive tables for your corporate databases.