Select Default Tablespaces for Oracle Users

Every Oracle database user has a default tablespace — the location where Oracle stores schema objects when no explicit tablespace is specified in a CREATE statement. Choosing an appropriate default tablespace is one of the first decisions a DBA makes when creating a user account. A poorly chosen default can lead to user objects landing in the SYSTEM tablespace, causing performance degradation through disk contention and mixing user data with Oracle's own data dictionary.

Why Default Tablespaces Matter

When a user issues a CREATE TABLE or CREATE INDEX statement without naming a tablespace, Oracle must decide where to store the object. The resolution order is:

1. The tablespace named explicitly in the CREATE statement, if provided
2. The user's assigned default tablespace, if one is defined
3. The database-level default permanent tablespace, if one has been set
4. The SYSTEM tablespace, as the final fallback

Standard SQL does not require a tablespace clause in CREATE TABLE, so Oracle accepts the statement regardless. The explicit tablespace clause is a best practice, not a requirement. The following example stores a table in a named tablespace explicitly:

CREATE TABLE test (
  x NUMBER) TABLESPACE coin_auction;

Without the TABLESPACE clause, Oracle falls through the resolution order above. If the user has no default tablespace defined and the database has no default permanent tablespace configured, the object lands in SYSTEM. The SYSTEM tablespace is heavily used by Oracle's data dictionary, and placing user objects there creates disk contention — a condition where multiple processes compete for the same disk, degrading I/O response times for all operations including critical dictionary lookups.

Assigning a Default Tablespace to a User

The default tablespace is specified at user creation time using the DEFAULT TABLESPACE clause:

CREATE USER app_user IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

For a user expected to own only a small number of objects — a reporting user or a limited application account — the shared USERS tablespace is typically appropriate. For a user expected to own large volumes of data, the DBA should create a dedicated tablespace and assign it as the default:

CREATE TABLESPACE app_data
  DATAFILE '/u01/oradata/orcl/app_data01.dbf' SIZE 500M AUTOEXTEND ON;

CREATE USER app_owner IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data;

The default tablespace can be changed at any time using ALTER USER:

ALTER USER app_user DEFAULT TABLESPACE new_tablespace;

Changing the default tablespace does not move existing objects — they remain in the tablespace where they were originally created. Only new objects created after the change are directed to the new default.

Database-Level Default Permanent Tablespace

Oracle allows a DBA to set a database-wide default permanent tablespace that applies to any non-SYSTEM user who is not assigned an explicit default. This is configured with the DEFAULT TABLESPACE clause of the CREATE DATABASE statement, or changed afterward with ALTER DATABASE:

ALTER DATABASE DEFAULT TABLESPACE users;

Oracle strongly recommends configuring a database-level default permanent tablespace. Without it, any user created without an explicit DEFAULT TABLESPACE clause will have their objects fall back to SYSTEM. In Oracle 19c and later, the database-level default is visible in DATABASE_PROPERTIES:

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

Default Temporary Tablespace

In addition to a permanent tablespace, each user requires a temporary tablespace for sort operations, hash joins, and other memory-spill workloads. If a user is not assigned a temporary tablespace and no database-level default temporary tablespace exists, Oracle assigns SYSTEM as the temporary tablespace — which is equally problematic as using it for permanent objects.

The database-level default temporary tablespace is set with the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE, or modified after creation:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

To create a new temporary tablespace and promote it to the database default:

CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/oradata/orcl/temp02.dbf' SIZE 200M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

All users not explicitly assigned a temporary tablespace are automatically switched to the new default. A default temporary tablespace cannot be dropped or taken offline while it is designated as the default — you must first assign a replacement, then drop or offline the former one. A temporary tablespace also cannot be converted to a permanent tablespace.

The current default temporary tablespace is visible in DATABASE_PROPERTIES:

SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Locally Managed Tablespaces and SYSTEM

When the SYSTEM tablespace is locally managed — which is the default and recommended configuration in Oracle 19c — Oracle enforces an additional restriction: it will not allow SYSTEM to be used as a temporary tablespace. In this configuration, Oracle automatically creates a default temporary tablespace during database creation if one is not specified. This behavior prevents the most common misconfiguration that causes temporary sort data to compete with data dictionary I/O.

Tablespace Quotas

Assigning a default tablespace does not by itself grant the user any space within it. Storage quotas must be granted separately. Without a quota, a user assigned to a tablespace can reference it as their default but cannot create any objects there:

-- Grant unlimited quota on the default tablespace
ALTER USER app_user QUOTA UNLIMITED ON users;

-- Grant a fixed quota
ALTER USER app_user QUOTA 500M ON app_data;

Setting QUOTA 0 on a tablespace prevents a user from creating objects there even if they hold the CREATE TABLE privilege. This is useful for restricting application users from creating objects in tablespaces they should only read from.

Verifying Tablespace Assignments

Query DBA_USERS to confirm a user's current tablespace assignments:

SELECT username,
  default_tablespace,
  temporary_tablespace
FROM dba_users
WHERE username = 'APP_USER';

To review quota allocations across all tablespaces for a user:

SELECT tablespace_name,
  bytes,
  max_bytes
FROM dba_ts_quotas
WHERE username = 'APP_USER';

Oracle 23ai Enhancements

Oracle 23ai introduces no fundamental changes to the tablespace assignment model, but its schema-level privilege enhancements interact with tablespace design. When granting schema-level privileges to application users, it becomes more important to ensure those users have appropriate quotas on the correct tablespaces, since schema-level grants can allow users to create objects they might not previously have been able to create. Reviewing tablespace quotas alongside privilege grants is recommended as part of any Oracle 23ai user provisioning workflow.

Summary

Every Oracle user should be assigned an explicit default permanent tablespace appropriate to their expected storage footprint — a shared USERS tablespace for lightweight accounts, a dedicated tablespace for data-intensive owners. The database-level default permanent and temporary tablespace settings provide a safety net for users created without explicit assignments, and should always be configured to prevent fallback to SYSTEM. Tablespace quotas must be granted separately from tablespace assignment. Use DBA_USERS and DBA_TS_QUOTAS to verify assignments and quotas after any user creation or modification.