REMAP_DATA Parameter of Oracle Data Pump Export (expdp)/ Import (impdp) - New Feature Oracle Database 11g
REMAP_DATADefault: There is no default
REMAP_DATAparameter 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_DATAfunction. 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.functionThe 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.
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
aand 8 columns on table
b, and so on, but the maximum for each table is 10.
The use of synonyms as values for the
REMAP_DATAparameter is not supported. For example, if the
regionstable in the
hrschema had a synonym of
regn, an error would be returned if you specified
regnas part of the
- Remapping LOB column data of a remote table is not supported.
- Create a table.
- Create a package Specification.
- Create a package body.
- Perform Export using expdp.
- Check Your Data is exported in encrypted form.
- Drop table to test import.
- Import table using impdp without remap_data parameter
- You table is imported but the address column data is encrypted.
- Drop again to check the impact of using remap_data parameter while importing,
- Import with remap_data parameter.
- Now check again your table is imported with decrypted original data.