From time to time it is necessary to remove sensitive data, e.g. credit card information, birth dates, names, etc. from tables. One way to accomplish this can be done is with parallel DML:

• The first step is to seed the random number generator.

begin dbms_random.seed(‘oiw3284js’);
end;
/

• Then, parallel DML has to be enabled as it is disabled per default. Note the different locking mechanism when DML is enabled.

ALTER SESSION ENABLE PARALLEL DML;

• Next, the update statement is executed. Charactar columns can be set with dbms_random.string whereas number values can be set with dbms_random.value.

UPDATE /*+ PARALLEL(C,8) */ CONTACT C
set EMAIL_ADDR  = dbms_random.string(‘A’, nvl(length(EMAIL_ADDR), 0)),
FAX_PH_NUM  = dbms_random.string(‘A’, nvl(length(FAX_PH_NUM), 0)),
FST_NAME    = dbms_random.string(‘A’, nvl(length(FST_NAME), 0)),
HOME_PH_NUM = dbms_random.string(‘A’, nvl(length(HOME_PH_NUM), 0)),
LAST_NAME   = dbms_random.string(‘A’, nvl(length(LAST_NAME), 0)),
WORK_PH_NUM = dbms_random.string(‘A’, nvl(length(WORK_PH_NUM), 0)),
–nicht verwendet in query
X_BC_NUMBER      = CASE WHEN nvl(length(X_BC_NUMBER), 0) > 0 THEN to_char(round(dbms_random.value(4917888006015004, 7081430000510616), 0)) ELSE NULL END,
X_ACCOUNT_HOLDER = dbms_random.string(‘A’,
nvl(length(X_ACCOUNT_HOLDER), 0)),
X_ACCOUNT_NUMBER = dbms_random.string(‘A’,
nvl(length(X_ACCOUNT_NUMBER), 0)),
X_PHOTO_URL      = CASE WHEN
nvl(length(X_PHOTO_URL), 0) > 0
THEN ‘https://www.myurl.de/’ || to_char(round(dbms_random.value(1111111111111111,  9999999999999999),0))
ELSE NULL END;

commit;

The professional solution to data anonymizing is the new ORACLE ENTERPRISE MANAGER 10 DATA MASKING PACK.