Default database tablespace

If the default tablespace is not specified during the database creation, it defaults to SYSTEM. But how do you know which tablespace is default for the existing database? Issue the following query:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

The DATABASE_PROPERTIES view shows some very important information, in addition to the default tablespace—such as the default temporary tablespace, global database name, time zone, and much more.


To change the database default permenantly/temporary tablespace, use the ALTER DATABASE statement,
like this:
ALTER DATABASE DEFAULT TABLESPACE users;ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;