Upgrade to Oracle 12.2.0.1: Check your DEFAULT temporary tablespaces

One of the reasons why I have this blog is simply to stay in touch with smart people using Oracle and telling me about issues I haven’t seen before. Thanks to Tyler Van Vierzen I learned that an upgrade to Oracle 12.2.0.1 may alter the default local temporary tablespace.

Upgrade to Oracle 12.2.0.1: Check your DEFAULT temporary tablespaces

Tyler did contact me on Twitter:

“Curious if you’ve seen this… Seeing new “local temp tablespace” set to SYSTEM for some users after 12.2 upgrade. But not all. No discernable pattern. Some null (so default to their temp tablespace), some set equal to their temp tablespace, and some bizarrely set to SYSTEM (not a temp ts obviously). Not a PDB.”

And no, I haven’t seen this before. I did some quick tests in my demo environment. After running some test upgrades from different source versions to Oracle Database 12.2.0.1 I had the same strange behavior:

select d.temporary_tablespace,
       d.local_temp_tablespace,  -- 12.2 only
       u.spare9,
       count(*) CT
from dba_users d
  inner join sys.user$ u on d.username = u.name
group by
  d.temporary_tablespace,
  d.local_temp_tablespace,  -- 12.2 only
  u.spare9
/

TEMPORARY_TABLESPACE LOCAL_TEMP_TABLESPACE     SPARE9         CT
-------------------- --------------------- ---------- ----------
TEMP                 SYSTEM                         0         27
TEMP                 TEMP                           3          7
TEMP                                                          88

The SPARE9 column is included here as the underlying problem seems to be caused by a malformed query in denv_mig.sql to rebuild DBA_USERS view.

The fix will now use the temp tablespace’s name instead of temp tablespace’s number when
updating default local temp tablespace.

What should you do?

Please check if any of your users has the LOCAL_TEMP_TABLESPACE as SYSTEM after the upgrade. If yes, then:

alter user TEST LOCAL TEMPORARY TABLESPACE TEMP;

Further information

In Oracle Database 12.2 we introduce Local Temporary Tablespaces as a new feature for RAC and Clusterware to differentiate between a temp tablespace on local or on shared storage.

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/logical-storage-structures.html#GUID-93848D41-A32B-494F-87A0-A090FF1B2E9A

Furthermore, the above issue is covered as:

• BUG 23715518 – APPSST12201::SYSTEM TABLESPACE IS MARKED AS LOCAL_TEMP_TABLESPACE AFTER UPGRADE (non public)

and fixed in Oracle Database 18c onwards.

–Mike