REMAP_DATA :-
This is a common requirement for DBA to export data from production for various purposes. In a restricted or production environment it’s a must to mask a particular data while exporting from production.
Syntax :- REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function
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.
Create a package in source database
create or replace package body datapump_remap_emp as function idcard(id varchar2) return varchar2 is begin return translate(id, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()_+-=\/ ', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); end; end; / 4 5 Package created. SQL> SQL> 2 3 4 5 6 7 8 9 10 Package body created.
REMAP_DATA :
[oracle@18c ~]$ expdp system/oracle directory=DATA_PUMP_DIR dumpfile=emp_prod18c.dmp logfile=emp_prod18c.log REMAP_DATA=hari.emp.id:sys.datapump_remap_emp.idcard
Export: Release 18.0.0.0.0 - Production on Tue Aug 21 02:09:18 2018
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02": system/******** directory=DATA_PUMP_DIR dumpfile=emp_prod18c.dmp logfile=emp_prod18c.log REMAP_DATA=hari.emp.id:sys.datapump_remap_emp.idcard
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SYSTEM"."SYS_EXPORT_SCHEMA_01" 263.0 KB 1456 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_02 is:
/u01/app/oracle/admin/prod18c/dpdump/emp_prod18c.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" successfully completed at Tue Aug 21 02:09:58 2018 elapsed 0 00:00:40
In impdp process,emp table column ID data is encrypted in target database.Import with REMAP_DATA parameter option,the column ID data of emp table is imported with decrypt original data.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba