Creating Users   «Prev  Next»

Lesson 4 Default Tablespaces
Objective Choose an appropriate default tablespace for a user in Oracle AI Database 26ai

Oracle Default Tablespace: Check and Change a User Assignment

A user's default permanent tablespace supplies the storage destination for ordinary tables created without a TABLESPACE clause. It does not grant storage quota, and changing it does not relocate existing data.

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

ALTER USER app_user DEFAULT TABLESPACE app_data;
ALTER USER app_user QUOTA 500M ON app_data;

The example assumes APP_DATA exists and that you are an authorized administrator connected to the user's PDB. Use USER_USERS to inspect your own assignment when DBA dictionary access is unavailable.

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

For an ordinary nonpartitioned table, an explicit TABLESPACE clause selects the destination; otherwise Oracle uses the schema owner's default. When creating a user without an explicit default, Oracle assigns the container's default permanent tablespace. This is a user assignment, not a fresh four-step fallback search on every CREATE statement. Partitioned objects and indexes have additional placement rules; do not assume every object uses the same default.

CREATE TABLE test (
 x NUMBER) TABLESPACE coin_auction;

Keep application data out of SYSTEM and SYSAUX. Check the actual user assignment instead of assuming it is USERS; tablespace names and defaults depend on how the PDB was provisioned.

Assigning a Default Tablespace to a User

Replace the illustrative password and filesystem path with approved environment-specific values. The tablespaces must exist. The default tablespace is specified at user creation time using the DEFAULT TABLESPACE clause:

CREATE USER app_user IDENTIFIED BY "ReplaceWithUniquePassword"
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA 100M 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 NEXT 100M MAXSIZE 5G;

CREATE USER app_owner IDENTIFIED BY "ReplaceWithUniquePassword"
 DEFAULT TABLESPACE app_data
 TEMPORARY TABLESPACE temp
 QUOTA 500M 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. New ordinary tables without explicit placement use the new default; existing segments require a separate move operation.

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;

This is a container-wide administrative change, not a substitute for changing one account with ALTER USER. Review existing user assignments before and after changing the container default; Oracle can reassign users as part of this operation. It does not move existing segments.

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

Temporary tablespaces provide workspace for operations such as sorts and hash joins when they spill to disk. They do not store ordinary permanent application tables. Inspect the user's temporary assignment and the PDB default; do not use SYSTEM for temporary work in a modern locally managed database.

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 generally cannot allocate permanent segment space there. Deferred segment creation can delay a quota error until the first insert. The UNLIMITED TABLESPACE system privilege overrides individual quotas:

-- 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;

QUOTA 0 prevents further space allocation when the user is subject to quotas; it does not remove existing objects or revoke access to rows. A user with UNLIMITED TABLESPACE is not constrained by that zero quota. Quotas apply to permanent storage, not temporary tablespaces.

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

Privileges and quotas answer different questions. A grant to read another schema's tables does not require the reader to have a permanent storage quota: the table owner's segments hold the data. A schema owner creating and extending objects needs both the appropriate privileges and storage allocation rights. Grant only the privileges and quota needed for the account's role.

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.

[1] disk contention: The problem resulting when multiple processes compete for access to the same disk, causing I/O requests to queue and response times to degrade for all operations sharing that disk.
[2] default permanent tablespace: A designated storage location where schema objects are placed when no explicit tablespace is named in the CREATE statement. Configured at the user level or, per container, at the container level, to keep user data out of the SYSTEM tablespace.

Oracle AI Database 26ai reference: CREATE USER; ALTER USER; ALTER DATABASE.

SEMrush Software 4 SEMrush Banner 4