Check and Change Default Tablespace for User in Oracle

1. Check the User default tablespace

select username,default_tablespace from dba_users where username = 'MDSYS';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
MDSYS SYSAUX

2. In this user MDSYS has the tablespace SYSAUS as default.

3. Change the user Default tablespace

SQL> alter user MDSYS default tablespace SPATIAL_TBS;
User altered.


4. Verify the result

select username,default_tablespace from dba_users where username = 'MDSYS';
USERNAME DEFAULT_TABLESPACE
---------- -------------------
MDSYS SPATIAL_TBS