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


Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Arun.

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

Answered by: Chris Saxon - Last updated: August 12, 2020 - 2:21 pm UTC

Category: Database Administration - Version: 19c

Viewed 100+ times

You Asked

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?


and we 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.

More to Explore


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