Assign Disk Quotas to Users

A tablespace quota is the amount of disk space a specific user is permitted to consume within a specific tablespace. Without a quota, a user assigned to a tablespace can reference it as their default but cannot create any objects there — even if they hold the CREATE TABLE privilege. Quota management is therefore a mandatory step in user provisioning, not an optional one. This lesson covers how to assign, modify, monitor, and revoke tablespace quotas in Oracle 19c and Oracle 23ai.

How Tablespace Quotas Work

Quotas operate independently per tablespace. A user may have different quota allocations across different tablespaces in the same database — a generous quota on their default application tablespace, a restricted quota on a shared reporting tablespace, and no quota at all on tablespaces they should never write to. The database enforces each quota independently. When a user's quota on a tablespace is exhausted, any statement that would cause their space consumption to exceed the limit fails with an ORA-01536: space quota exceeded error.

One important exception: temporary tablespaces do not require quotas. Oracle manages temporary space allocation automatically for sort and hash join operations regardless of whether a quota has been assigned. Assigning a quota on a temporary tablespace has no effect and is not necessary.

Assigning Quotas at User Creation

The QUOTA clause of CREATE USER assigns one or more tablespace quotas at account creation time. Multiple quotas can be specified in a single statement:

CREATE USER app_user IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA 500M ON app_data
  QUOTA 100M ON users;

To grant a user unlimited space on a tablespace — appropriate for schema owners or application accounts that manage their own objects — use QUOTA UNLIMITED:

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

Be deliberate about granting UNLIMITED TABLESPACE as a system privilege rather than per-tablespace unlimited quotas. The system privilege overrides all individual tablespace quota settings and grants the user unrestricted space across every tablespace in the database, including SYSTEM. Per-tablespace QUOTA UNLIMITED clauses are safer because they limit the user to specific tablespaces only.

Modifying Quotas After User Creation

Tablespace quotas can be increased, decreased, or revoked at any time using ALTER USER:

-- Increase quota
ALTER USER app_user QUOTA 1G ON app_data;

-- Reduce quota
ALTER USER app_user QUOTA 200M ON app_data;

-- Grant unlimited quota on a specific tablespace
ALTER USER app_user QUOTA UNLIMITED ON app_data;

-- Revoke quota entirely (user can no longer create objects here)
ALTER USER app_user QUOTA 0 ON app_data;

Setting QUOTA 0 does not remove objects the user has already created in that tablespace — existing objects remain intact. The user simply cannot create new objects or extend existing ones until the quota is restored. This makes QUOTA 0 a useful tool for temporarily freezing a user's storage growth without dropping their schema.

Verifying Quota Assignments and Usage

Three data dictionary views provide quota visibility at different scopes:

DBA_TS_QUOTAS shows quota assignments and current usage for all users across all tablespaces:

SELECT username,
  tablespace_name,
  bytes / 1024 / 1024 AS used_mb,
  max_bytes / 1024 / 1024 AS quota_mb,
  dropped
FROM dba_ts_quotas
WHERE username = 'APP_USER'
ORDER BY tablespace_name;

A MAX_BYTES value of -1 indicates unlimited quota. A MAX_BYTES value of 0 means the quota has been revoked.

USER_TS_QUOTAS shows the same information scoped to the current user's own quotas — useful for application accounts that need to check their own space availability:

SELECT tablespace_name,
  bytes / 1024 / 1024 AS used_mb,
  max_bytes / 1024 / 1024 AS quota_mb
FROM user_ts_quotas;

DBA_USERS does not show quota details directly, but combining it with DBA_TS_QUOTAS gives a complete picture of a user's storage profile:

SELECT u.username,
  u.default_tablespace,
  q.tablespace_name,
  q.bytes / 1024 / 1024 AS used_mb,
  q.max_bytes / 1024 / 1024 AS quota_mb
FROM dba_users u
LEFT JOIN dba_ts_quotas q ON u.username = q.username
WHERE u.username = 'APP_USER';

Quota Planning by User Type

Quota assignments should reflect the expected storage footprint of each user type:

1. Schema owners — application accounts that own all tables and indexes for an application. Assign QUOTA UNLIMITED on their dedicated tablespace. These accounts typically do not need quotas on any other tablespace.
2. Developer accounts — users who create objects in a development or test environment. Assign a generous but bounded quota, such as 2GB on a shared development tablespace, to prevent runaway object creation from filling shared storage.
3. Reporting and read-only users — users who only query data and never create objects. Assign QUOTA 0 on all tablespaces. These users do not need any quota and should be explicitly prevented from creating objects.
4. ETL and batch accounts — users that create and drop staging tables frequently. Assign a quota on a dedicated staging tablespace sized to the largest expected batch workload, separate from production tablespaces.

Flashback Data Archive Quotas

Oracle Flashback Data Archive (FDA) provides long-term, auditable change history for selected tables. FDA stores historical row versions in a dedicated tablespace, and the disk quota within the archive is bounded either by the tablespace size or by an explicit QUOTA limit set when the archive is created.

Best practice is to create a dedicated tablespace for each Flashback Data Archive rather than sharing tablespace with application data:

CREATE TABLESPACE fda_archive
  DATAFILE '/u01/oradata/orcl/fda01.dbf' SIZE 10G AUTOEXTEND ON;

Create archives with appropriate retention periods and optional quota limits:

-- No quota limit, 10-year retention (for compliance-critical tables)
CREATE FLASHBACK ARCHIVE fda_compliance
  TABLESPACE fda_archive
  RETENTION 10 YEAR;

-- 500MB quota, 7-year retention (for finance department tables)
CREATE FLASHBACK ARCHIVE fda_finance
  TABLESPACE fda_archive
  QUOTA 500M
  RETENTION 7 YEAR;

-- Default archive, 250MB quota, 2-year retention
CREATE FLASHBACK ARCHIVE DEFAULT fda_default
  TABLESPACE fda_archive
  QUOTA 250M
  RETENTION 2 YEAR;

The CREATE FLASHBACK ARCHIVE command accepts only one tablespace. To add additional tablespaces to an existing archive as its storage requirements grow, use ALTER FLASHBACK ARCHIVE:

ALTER FLASHBACK ARCHIVE fda_compliance
  ADD TABLESPACE fda_archive2
  QUOTA 5G;

Oracle 23ai Quota Enhancements

Oracle 23ai introduces schema-level privilege grants that interact directly with quota management. When a schema-level privilege is granted — allowing a user to create objects across all tables or views in a target schema — the grantee must still have an appropriate quota on the tablespace where those objects reside. Schema-level privilege grants do not implicitly grant storage rights. DBAs should review quota assignments whenever schema-level privileges are granted in Oracle 23ai to ensure users can exercise the privileges they have been given.

Oracle 23ai also refines the behavior of DBA_TS_QUOTAS to include clearer reporting on schema-level quota inheritance, making it easier to audit the effective storage permissions of application users in complex privilege environments.

Summary

Tablespace quotas control how much disk space each user may consume in each tablespace. They are assigned at creation with the QUOTA clause of CREATE USER and modified at any time with ALTER USER. Setting QUOTA 0 freezes storage growth without removing existing objects. Temporary tablespaces require no quota. Avoid granting the UNLIMITED TABLESPACE system privilege — use per-tablespace QUOTA UNLIMITED instead to limit unrestricted access to specific tablespaces only. Monitor quota usage through DBA_TS_QUOTAS and USER_TS_QUOTAS, and plan quota allocations based on the expected storage footprint of each user type.

Tablespaces Quotas - Quiz