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.