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.

Remapping Data


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics