Implicitly Assigned temp tablespace changes after database default tablespace change

In this post I have shown if you change your database default temporary tablespace then implicitly assigned users to previous temporary tablespace automatically assigned to the new database default temporary tablespace but the users to whom temporary tablespaces are explicitly assigned they are not shifted.

Let's start by seeing database default temporary tablespace which is temp4.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE'; 2

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP4

Now if you create user then that user will be automatically assigned to temporary tablespace temp4 if we don't assign any. We create two users named Test1 and Test2. Create Test1 with no temp clause but create test2 with temp clause to temporary tablespace temp.
SQL> create user test1 identified by t;
User created.

SQL> create user test2 identified by t temporary tablespace temp;
User created.

As TEST1 is not assigned any tablespace, so database default temporary tablespace is assigned to it. We can see it by,

SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1                          TEMP4
TEST2                          TEMP

Now create a new temporary tablespace temp3.

SQL> create temporary tablespace temp3 tempfile '/oradata2/temp3.dbf' size 10M;
Tablespace created.

Let's chnage the database default temporary tablespace to temp3.

SQL> alter database default temporary tablespace temp3;
Database altered.

Now we will see TEST1 temporary tablespace change but TEST2 temporary tablespace does not.
SQL> select username, temporary_tablespace from dba_users where username in ('TEST1','TEST2');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
TEST1                          TEMP3
TEST2                          TEMP

Related Documents
ORA-12906 cannot drop default temporary tablespace
Drop Temporary Tablespace Hangs
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.