Changing default tablespace for user in oracle

In my oracle database, one of the users was having its default tablespace as SYSAUX, and having default tablespace as SYSAUX is not a good idea in the oracle database, here we are going to discuss changing the default tablespace for oracle user

Let’s see how to check default tablespace using dba_users

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

-------------------- ------------------------------

TECHNO_USER          SYSAUX

 

Here we can see, default tablespace is SYSAUX, now change it USERS tablespace

SQL> alter user TECHNO_USER default tablespace USERS;

 

User altered.

 

verify the result

SQL> select username, default_tablespace from dba_users where username='TECHNO_USER';

 

USERNAME             DEFAULT_TABLESPACE

-------------------- ------------------------------

TECHNO_USER          USERS

 

In this we can simply alter default tablespace for any user.