— Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;

PROPERTY_VALUE
——————
ABC

Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.

— set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

— create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;

User created.

— change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1’;

DEFAULT_TABLESPACE
——————————
ABC

By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)