Select Default Tablespaces for Oracle Users in Oracle AI Database 26ai

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 default permanent tablespace of the container the user belongs to, 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 container 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.

The Default Permanent Tablespace Is a Per-Container Setting

Oracle allows a DBA to set a 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 this default. Without it, any user created without an explicit DEFAULT TABLESPACE clause will have their objects fall back to SYSTEM. It is visible in DATABASE_PROPERTIES:

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

This is worth understanding precisely, since it is easy to assume there is one single, database-wide value for this setting when there is not. Since every Oracle AI Database 26ai database is a multitenant container database, DATABASE_PROPERTIES is scoped to whichever container you are connected to when you query it: each PDB has its own default permanent tablespace setting, independent of every other PDB and of the CDB root. Confirmed directly in current documentation, the CDB-wide view, CDB_PROPERTIES, exists specifically because DATABASE_PROPERTIES alone only shows you the container you are currently in. If you manage several PDBs, checking or setting this default in one PDB tells you nothing about the others; you need to repeat the check, or query CDB_PROPERTIES, to see the full picture across the container.

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 the container has no default temporary tablespace configured, Oracle assigns SYSTEM as the temporary tablespace, which is equally problematic as using it for permanent objects.

The 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 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 while it is designated as the default; you must first assign a replacement, then drop the former one. A temporary tablespace also cannot be converted to a permanent tablespace.

Like the default permanent tablespace, this setting is per-container, visible in DATABASE_PROPERTIES for whichever PDB you are connected to:

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, the standard and recommended configuration, Oracle enforces an additional restriction: it will not allow SYSTEM to be used as a temporary tablespace at all. 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';

A value of -1 in MAX_BYTES specifically means the user has an unlimited quota on that tablespace, rather than a literal negative limit. DBA_TS_QUOTAS also includes BLOCKS and MAX_BLOCKS columns reporting the same information in Oracle blocks rather than bytes, and a DROPPED column indicating whether the tablespace itself has since been dropped.

Tablespace Design and Schema-Level Privileges

Current Oracle releases introduce no fundamental changes to the tablespace assignment model described above, but schema-level privilege grants, covered in Lesson 2, interact with tablespace design in a way worth keeping in mind. Granting broad schema-level privileges with a statement such as GRANT SELECT ANY TABLE ON SCHEMA hr TO app_user can allow a user to interact with far more of a schema than a handful of individually granted object privileges would, so it becomes more important to confirm that user has appropriate quotas on the correct tablespaces before granting privileges at that scope. Reviewing tablespace quotas alongside privilege grants is worth treating as a standard part of any user provisioning workflow, not an afterthought.

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 container-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; remember that these defaults are set per PDB, not once for an entire CDB, so they need checking in every container you manage. 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.