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 220.127.116.11 may alter the default local temporary tablespace.
Upgrade to Oracle 18.104.22.168: 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 22.214.171.124 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
SPARE9 column is included here as the underlying problem seems to be caused by a malformed query in denv_mig.sql to rebuild
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;
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.
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.