Hi Gurus
I have a unique requirement where I am asked to mask a test database here which contains about 2000+ tables with quite a few having more than 100 columns each. Since there isnt anyone who understands or knows about the sensitivity of each of these table's contents, the idea is to mask every character field and every date field that is held within the database. The second challenge is that I have to do this without any fancy Masking tools or even Oracle's own Data masking feature. Hence I am using dbms_obfuscation for text fields and add random numbers to date fields to mask them.
The main challenge is to break this up into tasks per table. Since for a given table A, at runtime, I will not be aware of the number of records or how many candidate columns are to be masked, I am using oracle's dbms_parallel_execute to break each table into chunks(based on rowid) and then for each chunk I loop through the columns that are candidates which I query at runtime and update them one by one. Hence for eg
Table A : 10000 Rows, 200 columns
Chunking may create 100 chunks with 100 rows each
For each chunk parse the table details in user tab columns into a cursor and for each column based on its datatype, perform the correct obfuscation
This means I end up performing 100*200 update operations over a period of time.
I know this isnt the best way. Also I am aware that I could store the counts and column names and datatypes externally for use later. But I am not able to avoid the number of updates mentioned above. I also thought of building up an sql per chunk to update all 200 columns in one go so that I only perform 100 update operations ie
for each chunk
update table a
set col1 = obfuscate(col1),
col2 = obfuscate(col2),....
col200 = obfuscate (col200);
but the variable size of the above sql means it could well fall over the limit of 32k that PLSQL allows
How else can i approach this to reduce my number of update operations?
Birdy