Skip to Main Content
  • Questions
  • Change local_temp_tablespace to shared TEMP

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Arun.

Asked: August 11, 2020 - 7:50 pm UTC

Last updated: August 12, 2020 - 2:21 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Hi,
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?

Thanks

and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database