DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01536 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01536: Space Quota Exceeded for Tablespace

ORA-01536 occurs when a database user has consumed all of the space quota allocated to them on a specific tablespace and attempts to create objects or insert additional data. Unlike ORA-01653 (which signals that the tablespace itself is out of physical space), this error is strictly about per-user quota limits enforced by the DBA. It is one of the most common errors encountered after creating new schemas or during high-volume batch operations.

Top 3 Causes

1. User Has Zero or No Quota Assigned

When a new user is created without an explicit quota grant, Oracle defaults the quota to 0, meaning the user cannot write a single byte to that tablespace.

-- Check quota for a specific user
SELECT USERNAME,
       TABLESPACE_NAME,
       BYTES / 1024 / 1024     AS USED_MB,
       MAX_BYTES / 1024 / 1024 AS QUOTA_MB
FROM   DBA_TS_QUOTAS
WHERE  USERNAME = 'YOUR_USERNAME';

-- MAX_BYTES = 0  means NO quota assigned
-- MAX_BYTES = -1 means UNLIMITED quota
Enter fullscreen mode Exit fullscreen mode

2. Existing Quota Limit Has Been Reached

A quota was set previously (e.g., 500 MB), but growing data — from batch inserts, logging tables, or index rebuilds — has exhausted that allocation.

-- Find users who have used 80% or more of their quota
SELECT USERNAME,
       TABLESPACE_NAME,
       BYTES / 1024 / 1024              AS USED_MB,
       MAX_BYTES / 1024 / 1024          AS QUOTA_MB,
       ROUND(BYTES / MAX_BYTES * 100, 2) AS USAGE_PCT
FROM   DBA_TS_QUOTAS
WHERE  MAX_BYTES > 0
  AND  (BYTES / MAX_BYTES * 100) >= 80
ORDER  BY USAGE_PCT DESC;
Enter fullscreen mode Exit fullscreen mode

3. RESOURCE Role Misunderstanding

Many DBAs assume that granting the RESOURCE role automatically provides UNLIMITED QUOTA on all tablespaces. In Oracle 12c and later, this behavior was changed — RESOURCE no longer implicitly grants unlimited quota, so it must be set explicitly.

-- This alone is NOT enough (common mistake)
GRANT RESOURCE TO your_user;

-- You must also explicitly grant quota
ALTER USER your_user QUOTA UNLIMITED ON your_tablespace;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Increase quota for an existing user:

-- Grant a specific size quota
ALTER USER app_user QUOTA 2048M ON app_data_ts;

-- Grant unlimited quota (use with caution in production)
ALTER USER app_user QUOTA UNLIMITED ON app_data_ts;
Enter fullscreen mode Exit fullscreen mode

Create a new user with quota included from the start:

CREATE USER new_schema
  IDENTIFIED BY "StrongPass!99"
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA 1024M ON app_data
  QUOTA UNLIMITED ON app_index;

GRANT CONNECT, RESOURCE TO new_schema;
Enter fullscreen mode Exit fullscreen mode

Verify the fix was applied:

SELECT USERNAME,
       TABLESPACE_NAME,
       MAX_BYTES / 1024 / 1024 AS NEW_QUOTA_MB
FROM   DBA_TS_QUOTAS
WHERE  USERNAME = 'NEW_SCHEMA';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Standardize User Creation Scripts
    Always include an explicit QUOTA clause in every CREATE USER statement. Maintain a team template that covers default tablespace, temp tablespace, quota, and role grants. Never rely on role inheritance for quota management.

  2. Automate Quota Monitoring
    Schedule a daily job using DBMS_SCHEDULER to query DBA_TS_QUOTAS and alert the DBA team when any user exceeds 80% of their allocated quota. This gives you a proactive window to expand quota before an application outage occurs.

-- Simple monitoring query to run daily
SELECT USERNAME,
       TABLESPACE_NAME,
       BYTES / 1024 / 1024                               AS USED_MB,
       MAX_BYTES / 1024 / 1024                           AS QUOTA_MB,
       ROUND(BYTES / MAX_BYTES * 100, 2) || '%'          AS USAGE_PCT
FROM   DBA_TS_QUOTAS
WHERE  MAX_BYTES > 0
  AND  (BYTES / MAX_BYTES * 100) >= 80
ORDER  BY (BYTES / MAX_BYTES) DESC;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01653 Unable to extend table — tablespace itself is physically full (different from per-user quota)
ORA-01950 No privileges on tablespace — user has zero quota assigned
ORA-30036 Unable to extend undo segment — undo tablespace space issue

Key Distinction: ORA-01536 = user-level quota problem. ORA-01653 = tablespace-level space problem. Always check the exact error code before taking corrective action.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)