Change default tablespace for User in Oracle

Check default tablespace for user

col username for a8
col default_tablespace for a18
col temporary_tablespace for a20
select username,default_tablespace,temporary_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
-------- ------------------  ---------------------
SYS      SYSTEM              TEMP
SYSTEM   SYSTEM              TEMP
HR       USERS               TEMP

Change user default tablespace

SQL> alter user hr default tablespace sysaux;
User altered.


Change temp default tablespace

SQL> alter user hr temporary tablespace temp2;
User altered.


Check Default tablespace for Instance
When any user created without specify the tablespace clause then it assigned to default tablespace.

col property_name for a30
col property_value for a15
SELECT property_name,property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_PERMANENT_TABLESPACE   USERS

Change the default tablespace for user data

alter database default tablespace users;


Check default TEMP tablespace

col property_name for a30
col property_value for a15
SELECT property_name,property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP