ORA-01536: Space Quota Exceeded for Tablespace - Manage User Quotas

ORA-01536: Space Quota Exceeded for Tablespace

Error Overview

Error Text: ORA-01536: space quota exceeded for tablespace 'string'

The ORA-01536 error is thrown when a database user attempts to create or extend a segment (table, index, LOB, etc.) in a tablespace but has reached their allocated quota limit for that tablespace. Unlike space-exhaustion errors such as ORA-01653, this error is not about the tablespace being full — it means the specific user has consumed their maximum permitted allocation. Other users may still have quota available in the same tablespace.

Common Causes

1. Quota Set Too Low for the Workload

• Initial quota was estimated conservatively and the application has grown beyond it
• A data load or bulk insert operation added more data than the quota allows
• Index creation for a large table pushed the user’s total allocation over the limit

2. No Quota Granted at All

• A new user was created but only granted CONNECT and RESOURCE (or equivalent roles) without an explicit tablespace quota
• Note: Granting the RESOURCE role used to implicitly grant UNLIMITED TABLESPACE in older Oracle versions, but this is no longer the case in 12c and later
• User has CREATE TABLE privilege but no quota on the default tablespace

3. Quota Reduced After Data Was Loaded

• A DBA reduced a user’s quota as a space-management measure without realizing the application would need to grow further
• Schema reorganization moved objects to a tablespace where the user has insufficient quota

4. Multiple Schemas Competing for Limited Quota Pool

• Several application schemas share a tablespace with individual quotas, and one schema grew unexpectedly
• A migration or data replication job inserted data into the wrong schema, consuming its quota

5. UNLIMITED TABLESPACE Privilege Revoked

• The UNLIMITED TABLESPACE system privilege was revoked from a user or role that previously held it, imposing zero quota on all tablespaces immediately
• Users granted UNLIMITED TABLESPACE through a role may lose it if the role assignment is revoked

Diagnostic Queries

Check the Affected User’s Quotas

-- View all tablespace quotas for the affected user
SELECT
  username,
  tablespace_name,
  ROUND(bytes / 1024 / 1024, 2)     AS used_mb,
  CASE max_bytes
    WHEN -1 THEN 'UNLIMITED'
    ELSE TO_CHAR(ROUND(max_bytes / 1024 / 1024, 2))
  END                                AS max_mb,
  CASE max_bytes
    WHEN -1 THEN 0
    WHEN 0  THEN 100
    ELSE ROUND(bytes / max_bytes * 100, 1)
  END                                AS pct_used
FROM dba_ts_quotas
WHERE username = UPPER('&username')
ORDER BY tablespace_name;

Check All Users’ Quotas Across All Tablespaces

-- Find any users who have reached or exceeded their quota
SELECT
  username,
  tablespace_name,
  ROUND(bytes / 1024 / 1024, 2)     AS used_mb,
  CASE max_bytes
    WHEN -1 THEN 'UNLIMITED'
    WHEN 0  THEN '0 (no quota)'
    ELSE TO_CHAR(ROUND(max_bytes / 1024 / 1024, 2))
  END                                AS max_mb,
  CASE
    WHEN max_bytes = -1 THEN 'OK (unlimited)'
    WHEN max_bytes = 0  THEN 'BLOCKED (zero quota)'
    WHEN bytes >= max_bytes THEN 'EXCEEDED'
    WHEN bytes >= max_bytes * 0.9 THEN 'WARNING (>90% used)'
    ELSE 'OK'
  END                                AS quota_status
FROM dba_ts_quotas
ORDER BY
  CASE
    WHEN max_bytes > 0 AND bytes >= max_bytes THEN 1
    WHEN max_bytes = 0 THEN 2
    WHEN max_bytes > 0 AND bytes >= max_bytes * 0.9 THEN 3
    ELSE 4
  END,
  username;

Check User’s System Privileges (for UNLIMITED TABLESPACE)

-- Check if the user has UNLIMITED TABLESPACE system privilege
SELECT
  grantee,
  privilege,
  admin_option
FROM dba_sys_privs
WHERE grantee = UPPER('&username')
  AND privilege = 'UNLIMITED TABLESPACE';

-- Also check roles the user holds that may grant unlimited tablespace
SELECT
  rp.grantee                        AS username,
  rp.granted_role,
  sp.privilege,
  sp.admin_option
FROM dba_role_privs rp
JOIN dba_sys_privs sp ON sp.grantee = rp.granted_role
WHERE rp.grantee = UPPER('&username')
  AND sp.privilege = 'UNLIMITED TABLESPACE';

Check What Segments the User Owns and Their Sizes

-- Summarize the affected user's segment usage by tablespace
SELECT
  owner,
  tablespace_name,
  segment_type,
  COUNT(*)                           AS segment_count,
  ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb
FROM dba_segments
WHERE owner = UPPER('&username')
GROUP BY owner, tablespace_name, segment_type
ORDER BY total_mb DESC;

-- Find the largest individual segments for the user
SELECT
  segment_name,
  segment_type,
  tablespace_name,
  ROUND(bytes / 1024 / 1024, 2)     AS size_mb,
  extents
FROM dba_segments
WHERE owner = UPPER('&username')
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

Check Default Tablespace for New Users

-- Review default tablespace assignments for all users
SELECT
  username,
  default_tablespace,
  temporary_tablespace,
  account_status,
  created
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY username;

-- Check if any users have a default tablespace of SYSTEM (a common misconfiguration)
SELECT username, default_tablespace
FROM dba_users
WHERE default_tablespace = 'SYSTEM'
  AND username NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')
ORDER BY username;

Step-by-Step Resolution

1. Increase the User’s Quota on the Specific Tablespace

This is the most common fix — simply grant more space to the user:

-- Grant a specific quota (in MB or GB)
ALTER USER &username QUOTA 2G ON &tablespace_name;

-- Or grant 500 MB
ALTER USER &username QUOTA 500M ON &tablespace_name;

-- Verify the change took effect
SELECT username, tablespace_name,
  ROUND(bytes/1024/1024,2) AS used_mb,
  ROUND(max_bytes/1024/1024,2) AS max_mb
FROM dba_ts_quotas
WHERE username = UPPER('&username');

2. Grant Unlimited Quota on a Specific Tablespace

For application schemas that should not be constrained by quota limits:

-- Grant unlimited quota on a specific tablespace
ALTER USER &username QUOTA UNLIMITED ON &tablespace_name;

-- This sets max_bytes = -1 in DBA_TS_QUOTAS, meaning no upper limit
-- The user is then only limited by the tablespace's total available space

3. Grant the UNLIMITED TABLESPACE System Privilege

For DBA accounts or schemas that need unrestricted access across all tablespaces:

-- Grant UNLIMITED TABLESPACE system privilege
GRANT UNLIMITED TABLESPACE TO &username;

-- Note: This overrides all per-tablespace quotas set via ALTER USER
-- Use with caution — a user with this privilege can consume ALL space
-- in ANY tablespace, including SYSTEM and SYSAUX

4. Revoke and Re-Grant After UNLIMITED TABLESPACE Was Revoked

If a previously working account lost access after a privilege change:

-- Check when quota was last changed (via audit trail if configured)
SELECT
  timestamp,
  db_user,
  action_name,
  object_name,
  sql_text
FROM dba_audit_trail
WHERE db_user = UPPER('&username')
  AND action_name IN ('ALTER USER', 'REVOKE')
ORDER BY timestamp DESC;

-- Re-grant the appropriate quota
ALTER USER &username QUOTA UNLIMITED ON users;
ALTER USER &username QUOTA UNLIMITED ON app_data;

5. Manage Quotas as Part of a User Provisioning Policy

For environments with strict space governance, define a standard quota policy for each user type:

-- Example: Grant standard quotas for an application schema
ALTER USER app_schema QUOTA UNLIMITED ON app_data;
ALTER USER app_schema QUOTA UNLIMITED ON app_indexes;
ALTER USER app_schema QUOTA 0 ON system;
ALTER USER app_schema QUOTA 0 ON sysaux;
ALTER USER app_schema QUOTA 0 ON users;

-- Example: Grant quotas for a developer account
ALTER USER dev_user QUOTA 500M ON dev_data;
ALTER USER dev_user QUOTA 100M ON dev_indexes;
ALTER USER dev_user QUOTA 0 ON system;

6. Reclaim Space If Quota Increase Is Not Appropriate

If the user genuinely accumulated too much data, identify and purge unnecessary objects:

-- Find tables that can potentially be truncated or have data purged
SELECT segment_name, segment_type,
  ROUND(bytes/1024/1024,2) AS size_mb
FROM dba_segments
WHERE owner = UPPER('&username')
  AND segment_type IN ('TABLE', 'TABLE PARTITION')
ORDER BY bytes DESC;

-- After purging data, reclaim space in the segment
-- (Truncate reclaims all space immediately)
TRUNCATE TABLE &username.&table_name;

-- Or shrink the segment after a DELETE
ALTER TABLE &username.&table_name ENABLE ROW MOVEMENT;
ALTER TABLE &username.&table_name SHRINK SPACE CASCADE;
ALTER TABLE &username.&table_name DISABLE ROW MOVEMENT;

Prevention Strategies

1. Proactive Quota Monitoring

-- Monitor users approaching their quota limits (>80% used)
CREATE OR REPLACE PROCEDURE monitor_ts_quotas AS
BEGIN
  FOR r IN (
    SELECT
      username,
      tablespace_name,
      ROUND(bytes/1024/1024, 2)     AS used_mb,
      ROUND(max_bytes/1024/1024, 2) AS max_mb,
      ROUND(bytes/max_bytes*100, 1) AS pct_used
    FROM dba_ts_quotas
    WHERE max_bytes > 0
      AND bytes >= max_bytes * 0.80
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(
      'QUOTA WARNING: User ' || r.username ||
      ' on tablespace ' || r.tablespace_name ||
      ' is at ' || r.pct_used || '% (' ||
      r.used_mb || ' MB of ' || r.max_mb || ' MB used)'
    );
  END LOOP;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_TS_QUOTAS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'monitor_ts_quotas',
    repeat_interval => 'FREQ=DAILY;BYHOUR=7',
    enabled         => TRUE,
    comments        => 'Daily check for users approaching tablespace quota limits'
  );
END;
/

2. Establish a Standard Quota Provisioning Template

-- Create a DBA procedure to provision new application schemas consistently
CREATE OR REPLACE PROCEDURE provision_app_schema(
  p_username      IN VARCHAR2,
  p_data_quota_gb IN NUMBER DEFAULT 10,
  p_idx_quota_gb  IN NUMBER DEFAULT 5
) AS
BEGIN
  -- Set quotas on relevant tablespaces
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username ||
    ' QUOTA ' || p_data_quota_gb || 'G ON app_data';
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username ||
    ' QUOTA ' || p_idx_quota_gb || 'G ON app_indexes';
  -- Explicitly block usage of system tablespaces
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' QUOTA 0 ON system';
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username || ' QUOTA 0 ON sysaux';

  DBMS_OUTPUT.PUT_LINE(
    'Provisioned ' || p_username ||
    ' with ' || p_data_quota_gb || 'G data quota and ' ||
    p_idx_quota_gb || 'G index quota.'
  );
END;
/

3. Configuration Best Practices

• Document the intended quota for every application schema in the change management system
• Use dedicated tablespaces per application so quota enforcement maps naturally to business units
• Avoid granting UNLIMITED TABLESPACE to non-DBA accounts — grant per-tablespace quotas instead
• When using Oracle Multitenant (CDB/PDB), manage quotas at the PDB level to contain application schemas
• Review DBA_TS_QUOTAS as part of regular schema review cycles and after any major data loads

Diagnostic Scripts

These Oracle Day by Day scripts can assist with schema and space analysis:

• dtable.sql — Table size and space usage analysis

Related Errors

• ORA-01950 - No privileges on tablespace
• ORA-01652 - Unable to extend temp segment
• ORA-01653 - Unable to extend table
• ORA-01654 - Unable to extend index
• ORA-01658 - Unable to create INITIAL extent

Emergency Response

Immediate Assessment

-- Quickly find which user, which tablespace, and how much quota is used
SELECT
  username,
  tablespace_name,
  ROUND(bytes/1024/1024,2)     AS used_mb,
  CASE max_bytes WHEN -1 THEN 'UNLIMITED' ELSE TO_CHAR(ROUND(max_bytes/1024/1024,2)) END AS max_mb
FROM dba_ts_quotas
WHERE username = UPPER('&username')
ORDER BY tablespace_name;

Immediate Actions

1. Increase quota immediately with ALTER USER ... QUOTA ... ON ... to restore service
2. Identify the triggering operation — review v$session and v$sql for the active session that received the error
3. Check for runaway data loads — a bulk insert or data migration may have consumed quota unexpectedly
4. Review whether UNLIMITED TABLESPACE was recently revoked — this is a common cause after security hardening exercises

Post-Resolution Cleanup

-- Document the new quota baseline in DBA_TS_QUOTAS
SELECT username, tablespace_name,
  ROUND(bytes/1024/1024,2) AS used_mb,
  CASE max_bytes WHEN -1 THEN 'UNLIMITED'
    ELSE TO_CHAR(ROUND(max_bytes/1024/1024,2)) END AS max_mb
FROM dba_ts_quotas
WHERE username = UPPER('&username');

-- Schedule quota monitoring to prevent recurrence
EXEC DBMS_SCHEDULER.ENABLE('MONITOR_TS_QUOTAS_JOB');