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 184.108.40.206 may alter the default local temporary tablespace.
Upgrade to Oracle 220.127.116.11: 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 18.104.22.168 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.
Thank you for the share.
Need blog details
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.
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.
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.
It looks like bug 23715518 is not fixed in 18c because patches on top of 18c have been released recently.
the bug says: (CHG: Fixed->18.1)
Why do you think it hasn’t been included?
Hi all – after 12.2 Upgrade (including redirecting LOCAL_TEMP_TABLESPACE away from system) altering a user’s password does again set LOCAL_TEMP_TABLESPACE to SYSTEM.
Wondering now, if this is a bug or by purpose.
Doesn’t sound like a feature to me.
More like a bug issues.
Do you have an SR number for it?
If not, please open an SR and send me the SR number please.
we implemented the workaround as described here:
to avoid patching our environment (>400 databases).
Thanks for sharing!
It seems like the issue is not fixed as support note 2385430.1 says. I have recently upgraded several databases from 11g to 19.3 and still seeing that user accounts are being assigned local temporary tablespace of NULL or SYSTEM or TEMP within the same PDB, depending on the value of spare9.
spare9=0 => SYSTEM
spare9=3 => TEMP
spare9=null => NULL
This is exactly the same observation that you have. So, the issue does not seem to be fixed. Do you have more information on this?
then I need to ask you to open an SR please with a reference to bug 23715518.
I checked, the code changes went into MAIN in Sept 2017 – this was the 18c code line. The issue shouldn’t occur in 19c anymore.
You can share the SR number with me please – then I can monitor and update the blog post.
In 19c, they have added the PASSWORD_CHANGE_DATE column to DBA_USERS. The script cdenv_mig.sql has been modified in 19c to accommodate this new column. Maybe it had some unintended consequences. I will post the SR number once I get some energy to open it…
Thanks for all your help.