Default Tablespace
The default tablespace in the database for all users is USERS if you didn't specify the default one for the user. This can be changed.
In this post, we'll talk about the way to set a different and newly created tablespace to an user.
Let's check the current temporary tablespace for the user.
SQL> select default_tablespace from dba_users where username = 'ERPAPP';
DEFAULT_TABLESPACE
------------------------------
USERS
Before you set the 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 erpapp default tablespace erptbs;
User altered.
We check the result.
SQL> select default_tablespace from dba_users where username = 'ERPAPP';
DEFAULT_TABLESPACE
------------------------------
ERPTBS
We have changed the default tablespace of the user.
To change the default permanent tablespace of the entire database, we should do it at database level.