Bug 18505843 TEMP_UNDO_ENABLED true

Problem Statement

Recently we have noticed global temporary tables (GTT) with the option “Preserve rows after commit” not working as expected post-migration to oracle 19c with the distributed transaction which uses database links as compared to 12c where it was working fine. Bug 18505843 TEMP_UNDO_ENABLED true is something to watch out for.

Observation

It has been identified with the 19c parameter temp_undo_enabled parameter is by default set to true which was false in 12c has an impact on the above behavior. To understand the reason we would need to understand about parameter temp_undo_enabled.

temp_undo_enabled parameter means undo generated for the changes to temporary tablespace will be written to permanent undo tablespace when it is set to false, when this parameter value is set to true it means undo generated for temporary tables will be written to temporary tablespaces. This will mean lesser redo log generation and better database performance. It also reduces the storage requirement due to lesser redo log generation.

In cases where this parameter is set to false which is default values in 12c, in that case, undo generated for temporary tablespace are stored in permanent undo tablespace and hence generated the redo data as well. Oracle always mains temp tables are not supported for distributed transactions i.e. remote transactions over database link.

Oracle has also logged one bug in this regard as “Bug 18505843 – TEMP_UNDO_ENABLED to true causing rows to be not preserved after commit over database link“. Though the Oracle development team has closed this as NOT a bug and expected behavior as distributed transactions are not supported for temporary tables. Although no errors were given in the past for this, these restrictions exist.

Solution

A strategic solution must be, not to use distributed transactions on temporary tables but it may not be easy for some to change the application logic due to various restrictions in place. In that case, set the parameter TEMP_UNDO_ENABLED as false. Bug 18505843 TEMP_UNDO_ENABLED true must be dealt with caution.