| Lesson 4 | Default Tablespaces |
| Objective | Choose an appropriate default tablespace for a user in Oracle AI Database 26ai |
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.
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.
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.
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.
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';
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.
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.
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.
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.
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.
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.