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