Enhance Skills Daily

October 03, 2026  ·   · 

Oracle DBA Lesson 44 Part 1 — Defaults, Quotas and Table Storage

Learn to identify a container's storage roles, find an account's effective defaults, and explain an allocation failure caused by quota.

Oracle DBA Lesson 44 Part 1 — Defaults, Quotas and Table Storage

Storage roles inside a pluggable database

RolePurposeWhat to inspect
SYSTEMData dictionary and essential database metadataKeep ordinary application data in its intended application tablespace
SYSAUXStorage for database componentsInspect component occupancy when investigating its growth
Application permanent tablespacePersistent application segments in physical data files on diskActual name, account defaults, segment locations, and allocation limits
Temporary tablespaceTemporary work, including sorts that spill to disk, in tempfilesAccount temporary assignment and the container's temporary storage
Undo tablespaceTransaction undo and read consistencyUndo mode. In local undo mode, each PDB has its own undo tablespace

A tablespace named USERS is not a requirement. CONTENTS identifies the storage type. SYSTEM and SYSAUX are permanent tablespaces with database-managed purposes.

SHOW CON_NAME

SELECT tablespace_name, contents
FROM dba_tablespaces
ORDER BY tablespace_name;

Confirm the intended container before interpreting these local dictionary results. From the root, CDB_TABLESPACES includes CON_ID. Retain that container identity when comparing like-named tablespaces. Which rows appear depends on the account, container access, and database state. See multitenant architecture and tablespaces and SYSAUX occupancy.

Container defaults and account defaults

SELECT property_name, property_value
FROM database_properties
WHERE property_name IN ('DEFAULT_PERMANENT_TABLESPACE',
                        'DEFAULT_TEMP_TABLESPACE');

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

The container's default permanent tablespace supplies a new account's default when CREATE USER omits that setting. An account can be assigned another permanent tablespace. For an ordinary table created in that account's schema without a TABLESPACE clause, Oracle uses the account's default. An explicit TABLESPACE clause selects the specified tablespace, subject to the relevant privileges, quota, and storage conditions. Special object types, partitions, and LOB storage can have additional placement rules. See CREATE USER and creating user accounts.

The container default is DEFAULT_DATA. APP_USER's default is APP_DATA, and its temporary assignment is APP_TEMP. Read the account's actual settings when predicting its ordinary table placement. The default is a placement setting. It does not grant CREATE TABLE or allocate quota. See DATABASE_PROPERTIES and DBA_USERS.

A quota failure despite free space

SELECT tablespace_name, bytes/1048576 AS charged_mib,
       max_bytes/1048576 AS quota_mib
FROM dba_ts_quotas
WHERE username='APP_USER';
TABLESPACE_NAMECHARGED_MIBQUOTA_MIB
APP_DATA2020

One MiB is 1,048,576 bytes. BYTES is space charged to the account. The charge covers its segments together in this tablespace, rather than only the ORDERS table or the amount of live row data. MAX_BYTES is the account's limit in bytes.

APP_DATA is online, the account has no UNLIMITED TABLESPACE privilege, and 40 MiB of suitable free extents remain. A further 1 MiB extent would take the account above its 20 MiB quota. Oracle rejects that allocation with ORA-01536 even though the tablespace has capacity. Diagnose the account limit before proposing file growth. Quota reserves no physical space. An account below its limit can still fail because usable capacity or another applicable limit is exhausted. See DBA_TS_QUOTAS.

Creating a table in one's own schema also requires CREATE TABLE. Growing an already existing table does not introduce a fresh requirement for that privilege. The operation still needs its own relevant privileges and must satisfy allocation limits. With deferred segment creation, a CREATE TABLE can succeed before physical segment allocation. A quota problem may appear when an insert triggers allocation. See CREATE TABLE.

Unlimited settings and missing rows

  • MAX_BYTES = -1 means unlimited quota for that particular tablespace. Inspect the original value before dividing it. -1/1048576 is not a useful displayed quota.
  • The UNLIMITED TABLESPACE system privilege overrides individual tablespace quotas. It is broader than an unlimited quota on one tablespace.
  • A missing DBA_TS_QUOTAS row alone cannot establish whether allocation is allowed. Check the effective privilege and the correct container as well.

Inspect a direct grant with:

SELECT privilege
FROM dba_sys_privs
WHERE grantee='APP_USER'
  AND privilege='UNLIMITED TABLESPACE';

UNLIMITED TABLESPACE is granted to users rather than roles. For the connected account, SESSION_PRIVS can help inspect effective session privileges. CREATE TABLE may be available through an enabled role in ordinary interactive SQL. Stored-code privilege rules need separate consideration.

A changed default leaves existing segments in place

If APP_USER's default later changes from APP_DATA to REPORT_DATA, the existing ORDERS segment remains in APP_DATA. Future ordinary tables that omit TABLESPACE use REPORT_DATA, provided the relevant allocation conditions there are met. Existing tables continue to use their existing segments. Changing the default does not redirect their later extent growth.

SELECT segment_name, tablespace_name
FROM dba_segments
WHERE owner='APP_USER' AND segment_name='ORDERS'
  AND segment_type='TABLE';
SEGMENT_NAMETABLESPACE_NAME
ORDERSAPP_DATA

Moving an existing segment requires a separately planned operation with its own object, index, availability, and capacity considerations. An empty table with deferred segment creation can have no DBA_SEGMENTS row. Inspect DBA_TABLES and SEGMENT_CREATED. Partitioned tables and their dependent segments require a broader inventory. See DBA_SEGMENTS.

Recap

Confirm the container, then read each tablespace role from CONTENTS and the account's own defaults. An ordinary table that omits TABLESPACE uses the account default. A finite quota can raise ORA-01536 while free extents remain, because the quota limits space charged to the account and does not reserve space. MAX_BYTES = -1 means unlimited quota on that tablespace, and UNLIMITED TABLESPACE overrides individual quotas, so a missing DBA_TS_QUOTAS row still needs a privilege and container check. Changing the default leaves existing segments, and their later extent growth, in the current tablespace.

Quiz

1. APP_USER's default is APP_DATA. An ordinary new table in that schema omits TABLESPACE. Where does it normally belong?

2. A user has 20 MiB charged against a finite 20 MiB quota, no overriding privilege, and needs a new 1 MiB extent. Its tablespace has 40 MiB free. What blocks this allocation?

3. Does changing a user's default tablespace move their current tables there?

4. What does MAX_BYTES = -1 mean in DBA_TS_QUOTAS?

5. A quota query returns no row. What must you check before concluding the user cannot allocate?

No comments:

Post a Comment

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...