How to find the default tablespace type (smallfile vs bigfile)

SQL> select property_value 
from database_properties 
where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE
---------------
SMALLFILE

Change like this:

alter database set default bigfile tablespace;

The change will of course only take effect for future tablespaces, not existing ones.


Link to Oracle documentation