How to Resolve ORA-01536: Space Quota Exceeded for Tablespace

The error ORA-01536: space quota exceeded for tablespace occurs in Oracle when a user tries to perform an operation (e.g., insert, update) that requires more space than they are allocated in a particular tablespace. This issue often arises due to improperly set quotas or when a user’s quota limit has been reached.

This blog post provides a practical, step-by-step guide to understanding and resolving the ORA-01536 error.

Step 1: Understand the Error

When this error occurs, it typically indicates:

• The user has a limited quota on the tablespace, and they’ve exceeded it.
• No quota has been assigned to the user for the tablespace.

To confirm this, check the error message details, which will mention the tablespace where the quota issue occurred.

Step 2: Identify the Affected User and Tablespace

You can use the following query to identify the user and their tablespace quota details:

SET PAGESIZE 50
SET LINESIZE 120
COLUMN tablespace_name FORMAT A20
COLUMN username FORMAT A20
COLUMN used_mb FORMAT 999,999
COLUMN max_allowed_mb FORMAT 999,999
SELECT 
    tablespace_name, 
    username, 
    ROUND(bytes / 1024 / 1024) AS used_mb, 
    ROUND(max_bytes / 1024 / 1024) AS max_allowed_mb
FROM 
    dba_ts_quotas
WHERE 
    username = 'USERNAME';

Replace USERNAME with the name of the user experiencing the issue.

If max_allowed_mb is 0, it means the user has no quota assigned for the tablespace.

Step 3: Check Tablespace Usage

Before modifying quotas, verify if the tablespace itself has free space. Use the following query:

SET PAGESIZE 50
SET LINESIZE 120
COLUMN tablespace_name FORMAT A20
COLUMN used_mb FORMAT 999,999
COLUMN free_mb FORMAT 999,999
COLUMN total_mb FORMAT 999,999
SELECT 
    tablespace_name, 
    ROUND(SUM(bytes)/1024/1024) AS total_mb, 
    ROUND(SUM(bytes)/1024/1024) - ROUND(SUM(maxbytes)/1024/1024) AS used_mb, 
    ROUND(SUM(maxbytes - bytes)/1024/1024) AS free_mb
FROM 
    dba_data_files
GROUP BY 
    tablespace_name;

Step 4: Resolve the Quota Issue

Option 1: Increase the User’s Quota

If the user has a quota set but has reached the limit, increase their quota using the following command:

ALTER USER USERNAME QUOTA unlimited ON TABLESPACE_NAME;

To assign a specific quota, use:

ALTER USER USERNAME QUOTA 500M ON TABLESPACE_NAME;

Option 2: Assign Quota to the User

If the user has no quota assigned, assign one using:

ALTER USER USERNAME QUOTA unlimited ON TABLESPACE_NAME;

Option 3: Free Up Space in the Tablespace

If the tablespace itself is full, you can either:

Add a new datafile to the tablespace:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 1G;

Extend an existing datafile:

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 2G;

Step 5: Verify the Solution

After making changes, verify that the issue has been resolved:

1. Check the updated quota using the query from Step.2
2. Perform the operation that caused the error to ensure it now succeeds.

Step 6: Prevent Future Issues

To avoid similar issues in the future:

1. Monitor Tablespace Usage Regularly: Use the query in Step 3 to check for tablespaces nearing their capacity.
2. Set Appropriate Quotas: Assign sufficient quotas to users based on their requirements.
3. Enable Autoextend for Datafiles: Ensure critical tablespaces have datafiles with AUTOEXTEND enabled.

Summary

The ORA-01536: space quota exceeded for tablespace error is caused by insufficient quotas or a lack of space in the tablespace. By identifying the affected user and tablespace, modifying quotas, or extending the tablespace, you can quickly resolve the issue. Regular monitoring and proactive quota management can prevent similar problems in the future.