Oracle: Find Default Permanent Tablespace using Database_Properties

To prevent people from accidentally having their own database objects in the SYSTEM tablespace Oracle introduced a default permanent tablespace because it is not healthy in terms of usability point of view to keep your own objects in SYSTEM tablespace.

Every database users allocated a default permanent tablespace as USERS to store their own objects like tables and indexes.

Sometimes Default Permanent Tablespace Set to a System Tablespace, it means that default tablespace for newly created user will assign this tablespace.

To find Default Permanent Tablespace:

SQL> select PROPERTY_VALUE from database_properties
  2  where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
---------------
USERS

 If it is system tablespace then change it as:

sql>alter database default tablespace users;