In order to mask data I am using REMAP_DATA parameter in Oracle datapump as follows:

expdp \'/ as sysdba\' DIRECTORY=DUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log SCHEMAS=user0 REMAP_DATA=user0.table1.col1:user0.pckg_mask.fun_mask REMAP_DATA=user0.table2.col2:user0.pckg_mask.fun_mask REMAP_DATA=user0.table1.col3:user0.pckg_mask.fun_mask

This is just an example. Instead of creating and deploying individual functions for different columns I am using fucnction overload method hence using the same function name but with different input parameter datatype.

But some columns need special logic for masking and the logic is stored in a central table.

So, is it possible that I can know which column is being processed by datapump and from which column it will take export next? (So that I can deploy the logic at run-time for that column which is being exported currently)

The question might be vague or complicated. Please do ask for needed information or any suggestion for improving question quality.

  • 1
    Expdp happens at table/row-level, not column level. i.e. it exports rows at individual columns. As a row is processed, it processes all columns for remap in that row. It will tell you which object it is going to process next (and no you won't have time to interject and change code on the fly). Is there a problem in pre-creating those column specific functions beforehand? what part are you trying to optimize here? – Raj Aug 17 '17 at 13:37
  • @Raj Thanks for your opinion. I am trying to employ this technique for large number of databases and many customers. I have a central table in which the data masking rules(logic) for each column are present. Instead of creating specific function for each column I am considering function overloading but the masking logic will change depending on column . Hence the column name is needed to detect which logic to deploy. Also, if I get the column name then I can use different masking logic for different group of columns. – Bibhuti Bhusan Padhi Aug 17 '17 at 14:39
  • 1
    As far as my experience with datapump goes, you won't have that kind of fine grain control during execution. Any control you have, exists before execution begins. couldn't you write the function so it accepts owner,table_name and column_name as parameter and then applies the logic? – Raj Aug 17 '17 at 16:20
  • @Raj As part of post-migration/import into QA yes that can be done as the export will be from Production. I was trying to find any info from Oracle datapump master table but no luck. Thank you very much!! – Bibhuti Bhusan Padhi Aug 17 '17 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.