Default Temporary Tablespace
Normally, all users in the database use the same default temporary tablespace except that we specifically set another one for them.
In this post, we'll talk about the way to set a different and newly created temporary tablespace to an user.
Let's check the current temporary tablespace for the user.
SQL> select temporary_tablespace from dba_users where username = 'HR';
TEMPORARY_TABLESPACE
------------------------------
TEMP
Before you set the temporary tablespace for the user, you should make sure the tablespace you're going to specify does exist.
Then we alter the user's attribute of default temporary tablespace as below.
SQL> alter user hr temporary tablespace temp1;
User altered.
We check the result.
SQL> select temporary_tablespace from dba_users where username = 'HR';
TEMPORARY_TABLESPACE
------------------------------
TEMP1
We have changed the default temporary tablespace of the user.
To change the default temporary tablespace of the entire database, we should do it at database level.