I am trying to find the downside of setting local_temp_tablespace to TEMP tablespace which is a shared temp.
The reason is because of a bug, if local_temp_tablespace is NULL and dba_users.spare9 is NULL, then Oracle assigns SYSTEM tablespace as local_temp_tablespace when I issue alter user command. For example, if a user AGUPTA has spare9 as NULL in DBA_USERS and local_temp_tablespace is currently NULL and I issue the command to change password:
alter user AGUPTA identified by xpS2Z^4%g%0h;
Then, the local_temp_tablespace for AGUTPA changes to SYSTEM. This is not good.
Mike Dietrich has a blog post about it. So, we did a small test and found that if we switch all users who have NULL for local_temp_tablespace to use TEMP tablespace, then the issue does not appear. The local_temp_tablespace stays at TEMP when changing password.
So, my question is: Is there a downside to changing every user's local_temp_tablespace to shared TEMP?
The local temp tablespace for a user defaults to the database's shared temp tablespace.
So setting every user's local temp to TEMP is the same as going with the default - i.e. what I expect 95%+ of databases use.
=> I see no issues with doing this.