Oracle: ORA-01652 crashes my Privacy run

The Oracle ORA-01652 error code indicates that the database has run out of temporary table space, and is trying to expand it during the running of a query, and is unable to do so.

SQL

SQL Error: ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
       a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
       files to the tablespace indicated.

Cause

This error is caused by running a Privacy run that is too large for the amount of space in the TEMP database table, or by issues relating to OracleDB’s dynamic generation of execution plans not handling the creation and filling of a large amount of temporary tables during a Privacy run. Sometimes, the database will mistake a large data set for being empty, and then implement the wrong execution plan, causing performance issues.

Resolution

This issue can be resolved by checking the size of the largest table in your selection and comparing that to the size of the TEMP table.

Our guideline for this is having at minimum the TEMP table be 2,5x bigger in size than the largest table your source database uses. If the TEMP tablespace is smaller than this criteria, contact your DBA and request that they increase amount of memory dedicated to the TEMP tablespace.

If this does not resolve the issue, open the following menus at the top of the screen after selecting the table that has caused the run to stall (usually, this is the table in the log file that throws the ORA-01652 error) Privacy → Masking → advanced settings → TempStatisticsPercentage

now raise the TempStatisticsPercentage to “10”.

What this does is instruct the program to make statistics about the size and characteristics of the temporary tables it creates, as well as other things. This is a backbone that the Oracle Database can use to prevent it from looking at certain queries and making radically wrong execution plans. Usually this does not occur, and thus the default for this option is empty.