DATAPUMP's REMAP_DATA OPTION

                                      ORACLE 11g DATAPUMP REMAP_DATA OPTIONS

The REMAP_DATA parameter allows you to specify a remap function that takes as a source the original value of the designated column and returns a remapped value that will replace the original value in the dump file. A common use for this option is to mask data when moving from a production system to a test system. For example, a column of sensitive customer data such as credit card numbers could be replaced with numbers generated by a REMAP_DATA function. This would allow the data to retain its essential formatting and processing characteristics without exposing private data to unauthorized personnel.

The same function can be applied to multiple columns being dumped. This is useful when you want to guarantee consistency in remapping both the child and parent column in a referential constraint.

Syntax and Description

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

The description of each syntax element, in the order in which they appear in the syntax, is as follows:

schema -- the schema containing the table to be remapped. By default, this is the schema of the user doing the export.

tablename -- the table whose column will be remapped.

column_name -- the column whose data is to be remapped. The maximum number of columns that can be remapped for a single table is 10.

schema -- the schema containing the PL/SQL package you have created that contains the remapping function. As a default, this is the schema of the user doing the export.

pkg -- the name of the PL/SQL package you have created that contains the remapping function.

function -- the name of the function within the PL/SQL that will be called to remap the column table in each row of the specified table.

Restrictions

• The datatypes of the source argument and the returned value should both match the data type of the designated column in the table.
• Remapping functions should not perform commits or rollbacks except in autonomous transactions.
• The maximum number of columns you can remap on a single table is 10. You can remap 9 columns on table a and 8 columns on table b, and so on, but the maximum for each table is 10.

Examp:

Our scenario is while taking emp table export , dumpfile will get new salary information .

new salary will be salary plus 10.  Here we dont want to disclose original value of sal column.  

Step1 :

=====

$sqlplus "/as sysdba"

SQL>create user dpusr identified by dpuser;

SQL>grant create session,resource,datapump_exp_full_database to dpusr;

SQL>select * from dba_directories;

OWNER                          DIRECTORY_NAME

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

DIRECTORY_PATH

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

SYS                            DATA_PUMP_DIR

/u01/app/oracle/admin/bill/dpdump/

SYS                            ORACLE_OCM_CONFIG_DIR

/u01/app/oracle/product/11.2.0/ccr/state

Step 2:  Describe the EMP table

desc emp

Name     Null Type         

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

EMPNO         NUMBER(4)    

ENAME         VARCHAR2(10) 

JOB           VARCHAR2(9)  

MGR           NUMBER(4)    

HIREDATE      DATE         

SAL           NUMBER(7,2)  

COMM          NUMBER(7,2)  

DEPTNO        NUMBER(2)  

Step 2:  Create package and function.

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

$sqlplus dpusr/dpusr;

create or replace package modify_sal_pkg as  function modify_sal_fun (sal  number)  return number;  end;

/

Package is created.

create or replace package body modify_sal_pkg as function modify_sal_fun(sal in number) return number

as 

sals number;

begin

sals :=sal+10;

return(sals);

end;

end;

/

Step3: Take the export

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

[oracle@test dp]$ expdp directory=dps dumpfile=dps%U.dmp tables=emp remap_data=dpusr.emp.sal:dpusr.modify_sal_pkg.modify_sal_fun

Export: Release 11.2.0.3.0 - Production on Tue Sep 17 09:47:22 2013

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

Username: dpusr

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "DPUSR"."SYS_EXPORT_TABLE_01":  dpusr/******** directory=dps dumpfile=dps%U.dmp tables=emp remap_data=dpusr.emp.sal:dpusr.add_sal.mod_sal

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "DPUSR"."EMP"                               8.578 KB      14 rows

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

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

Dump file set for DPUSR.SYS_EXPORT_TABLE_01 is:

 /u01/app/oracle/admin/bill/dpdump/dps01.dmp

Job "DPUSR"."SYS_EXPORT_TABLE_01" successfully completed at 09:47:37

Step 4:  Check the current value before Import

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

[oracle@test dp]$ sqlplus dpusr/dpusr

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 17 10:17:00 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select empno,sal from emp;

     EMPNO        SAL

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

      7369        800

      7499       1600

      7521       1250

      7566       2975

      7654       1250

      7698       2850

      7782       2450

      7788       3000

      7839       5000

      7844       1500

      7876       1100

     EMPNO        SAL

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

      7900        950

      7902       3000

      7934       1300

14 rows selected.

Step 5:  Delete the records from EMP table;

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

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

Step 6: Import the data from dumpfile;

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

[oracle@test dp]$ impdp tables=emp directory=dps dumpfile=dps01.dmp content=data_only

Import: Release 11.2.0.3.0 - Production on Tue Sep 17 10:20:30 2013

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

Username: dpusr

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "DPUSR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "DPUSR"."SYS_IMPORT_TABLE_01":  dpusr/******** tables=emp directory=dps dumpfile=dps01.dmp content=data_only

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DPUSR"."EMP"                               8.578 KB      14 rows

Job "DPUSR"."SYS_IMPORT_TABLE_01" successfully completed at 10:20:40

Step 7: verify the data

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

SQL> select empno,sal from emp;

     EMPNO        SAL

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

      7369        810

      7499       1610

      7521       1260

      7566       2985

      7654       1260

      7698       2860

      7782       2460

      7788       3010

      7839       5010

      7844       1510

      7876       1110

     EMPNO        SAL

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

      7900        960

      7902       3010

      7934       1310