Check and Change Default Tablespace for User in Oracle
- Check the User default tablespace
select username,default_tablespace from dba_users where username = 'MDSYS';
USERNAME DEFAULT_TABLESPACE
-------- ------------------
MDSYS SYSAUX
-
In this user MDSYS has the tablespace SYSAUS as default.
-
Change the user Default tablespace
SQL> alter user MDSYS default tablespace SPATIAL_TBS;
User altered.
- Verify the result
select username,default_tablespace from dba_users where username = 'MDSYS';
USERNAME DEFAULT_TABLESPACE
---------- -------------------
MDSYS SPATIAL_TBS