Remap Column Data on the Fly using EXPDP

Remap data is a new feature introduced in 11g releases which allows data on a column to be changed on the fly when exporting (expdp).

Few things to note. The help option gives the usage of remap_data as

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

It shows only the table name and column name is required but this result in following error

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39146: schema "" does not exist

Schema name is also required.

Also the function used to remap the data must accept the column type as argument otherwise following error will be thrown

ORA-31693: Table data object "ASANGA"."X" failed to load/unload and is being skipped due to error:
ORA-06553: PLS-306: wrong number or types of arguments in call to function_name

Also the function used must be a function within a package. Schema level function will not be used and following error will be shown

ORA-39001: invalid argument value
ORA-39232: invalid remap function: DUMP_OBFUSCATE

Below is an example package function that could be used

create or replace package obfuscate as

 function dump_obfuscate(ssn in number) return number;
 
end;
/

create or replace package body  obfuscate as
  
 function dump_obfuscate(ssn in number) return number
 is
  i number;
 begin
  i := dbms_random.value(0,100);
  return  round(i);
 end;

end;
/

Use the expdp syntax as

expdp asanga/asa directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate

It is also possible to remap data in multiple tables using multiple remap functions.

expdp asanga/*** directory=dumpdir dumpfile=asa.dmp logfile=asa.log SCHEMAS=asanga
REMAP_DATA=asanga.x.a:obfuscate.dump_obfuscate,asanga.y.a:obfuscate1.dump_obfuscate1