Home » Check and Change Default Tablespace for User in Oracle

Check and Change Default Tablespace for User in Oracle

by tuanlp

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
  1. In this user MDSYS has the tablespace SYSAUS as default.

  2. Change the user Default tablespace

SQL> alter user MDSYS default tablespace SPATIAL_TBS;
User altered.
  1. Verify the result
select username,default_tablespace from dba_users where username = 'MDSYS';
USERNAME DEFAULT_TABLESPACE
---------- -------------------
MDSYS SPATIAL_TBS

You may also like