Skip to main content
Delphix

How to Resolve ORA 00001 Errors During Masking

Issue

The intention: To mask the zip code AND the additional 4 numbers that followed.

The result: the algorithm was not designed to mask the entire zip code and the +4 (Zip+4). It was only designed to mask the +4 portion of the zip code, which resulted in non-unique values and the ORA-00001: ORA-00001 unique constraint violation errors cause Masking job to fail.

When a rule set is defined, an empty inventory is automatically created. The columns with sensitive data must be manually edited in the inventory (by clicking the pencil icon at the end of the inventory row), to assign a domain and apply a masking algorithm.

Looking at a ZIP+4 example that resulted in ORA-00001.

Troubleshooting

The ORA-00001 error is generated when unique constraints are set on a table column and the masked result does not create a unique value.
It also may happen that there is an index associated with a table column, which acts as an invisible constraints. 

The Job Monitor log shows: 

dmsuite.log.22:2016-10-24 11:09:03,522 INFO  com.dmsuite.dmsApplicator.masking.transformation.RunServiceImpl - [JOB_ID_250] ORA-00001: unique constraint (OPR_MSD.PK_ZIPCITY) violated

The error is indicating that an ORA-00001 was generated when the ZIPCITY column from OPR_MSD was being Masked.

  1. Select the Inventory Tab and edit the ZIPCITY Column by clicking the pencil, to the right, to see which Domain/Algorithm was assigned to the column.
  2. Then take a look at the Settings tab and select Domains, to see what the default Algorithm is associated with each Domain. The ZIP Domain's default Algorithm is ZIP+4 
    1. If you select Algorithms from the Settings Tab, you will see a list of the possible Algorithms that are predefined.
    2. In the case of the ZIP+4 Algorithm, the Description indicates 'Mask the "+4" component of ZIP'
    3. So in order to Mask the full zip code and the +4 component, this Algorithm will not work as it only Masks the +4 portion of the zip code and does not produce unique results.

Resolution

Solution

In order to obtain a Masking result that meets the Unique Constraint required for the column, a new Algorithm must be created.

The example below shows a new Algorithm (FullZip+4) that would result in Masking the zip code AND the +4 resulting in unique values.

The new Algorithm uses a Numeric 324 Segmented Mapping Algorithm.

Note that the ignore characters box shows that the first ignored character is a space, then a period and then a dash. Each "Ignored Characters" is separated by a comma.

The space and the period and the dash were included in this example, because the input file included spaces and periods and a dash in the values within the ZIPCITY column. Ignoring these characters means that only the numbers. that makeup the zip code +4 values will be masked while any spaces, periods or dashes will remain in the Masked values.

The result is that the first 3 numbers of the zip code will be Masked, then the next 2 numbers will be Masked and the the +4 numbers will be Masked. If the columns already met the Unique Constraint prior to Masking, the Masked result will as well.

Once the new Algorithm has been created, assign it to the ZIPCITY column.

Select the Inventory Tab and edit the ZIPCITY column by clicking the pencil to the right. 
For this example, the Domain is set to Zip and the Algorithm is set to the default (ZIP+4).

Select the Algorithm pull down and select your newly created Algorithm (FullZip+4).

Workaround:

Disable/drop constraints and drop indexes attached to the columns selected for masking.
If the algorithm used for masking doesn't guarantee unique generated values, indexes must be recreated without the UNIQUE option and constraints enabled with the NOVALIDATE option.

 

  • Was this article helpful?