How to See and Change Database Default Tablespace.

Have you seen that after we create a database manually (by default option) whenever we create a user the default tablespace is assigned automatically to SYSTEM tablespace.

But after we create a database by dbca whenever we create a user the default tablespace is assigned automatically to USERS tablespace.

Why they differs between these two. It is because of the settings of database default tablespace. Whenever we create a database manually (by default option) database default tablespace is assinged to SYSTEM tablespace.

But whenever we create a database by dbca database default tablespace is assigned to USERS tablespace.

We can see the default tablespace of the database by querying database_properties object.

Like,

SQL> select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TABLESPACE%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS


Now we can easily change the database default tablespace settings by,
alter database default tablespace

After setting new default tablespace any newly created user will be automatically assigned to new default tablespace.

Workaround Example:
------------------------
SQL> create user test1 identified by t;

User created.

SQL> select default_tablespace from dba_users where username='TEST1';

DEFAULT_TABLESPACE
------------------------------
USERS

SQL> alter database default tablespace DATA01;

Database altered.

SQL> create user test2 identified by t;

User created.

SQL> select default_tablespace from dba_users where username='TEST2';

DEFAULT_TABLESPACE
------------------------------
DATA01