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

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.

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

7 thoughts on “Upgrade to Oracle 12.2.0.1: Check your DEFAULT temporary tablespaces

  1. Pingback: New local temporary tablespace in 12.2 defaults to SYSTEM for some users after upgrade – DBA n00b

  2. Thanks for the article. I notice that temp tablespaces in 12c have a maxsize of 32767M (probably “unlimited”). My servers have very limited disk space so I need to set the maxsize to 4096M. I was able to to do that for my pluggable database, but I am unable to do that for my container database. Here are my steps: 1) Create TEMP2 tablespace with maxsize of 4096M. 2) Make TEMP2 tablespace the default temporary tablespace. 3) Drop original TEMP tablespace (including contents and datafiles). I cannot do Step3 in my container database. The command just hangs. I would also like to set the maxsize for the TEMP tablespace (as well as other tablespaces) in PDB$SEED so new pluggable databases would be sized correctly.

    • Dan,

      you must close the pluggable databases or at least restart the instance or at least disconnect your user first. My suspicion without further testing:
      The currently connected user has a handle on the “old” temporary tablespace unless you don’t connect again.

      What I did:

      create temporary tablespace temp123 tempfile ‘/u02/oradata/CDB2/temp123.db’ SIZE 500m autoextend on next 10m maxsize unlimited;

      ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP123;

      And when I tried to drop TEMP I received a hang as you.

      I quickly restarted my database and then i could drop TEMP. But it may be only a connect/reconnect necessary. Give it a try and update me if you have a second.

      Cheers,
      Mike

  3. Mike-
    I shut down the database then started the database. I was able to drop the TEMP tablespace successfully. Thanks for the advice. I was also wondering if I could just resize and set the maxsize of the TEMP tablespace right after database creation (alter database tempfile ‘/oradata1/CGIS1/temp01.dbf’ resize 500M autoextend on next 1M maxsize 4G). Then I wouldn’t have to create a TEMP2 tablespace, switch default temporary tablespace, etc… I am scheduling another 12c database creation and I am going to try out this new plan.
    Thanks again for your help on this.
    Dan

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.