How to Fix "ORA-01536: space quota exceeded for tablespace"

This is a common problem faced by developers working with Oracle databases. The error code ORA-01536 means that the allocated space for the tablespace is already used up.

To fix the error ORA-01536: space quota exceeded for tablespace 'TBNAME', you just have to extend the user quota of the tablespace.

Step 1 - Login to sqlplus Console

Login to your oracle server and connect to sqlplus

$ sqlplus / as sysdba
Output:

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 2 20:27:54 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>

Step 2 - View the Amount of Space Used by Tablespace

To view the amount of space allocated to a tablespace and the amount of space used:

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name;

Step 3 - Extend the space allocation of the tablespace to the user.

Resize the amount of space allocated for the user for the tablespace:

alter user <username> quota 200M on <tablespace>
Replace the <username> with the user whom the space allocation need to be changed and <tablespace> with the corresponding tablespace name.

To allow unlimited quota:

alter user <username> quota unlimited on <tablespace>
Asha

Asha

I am a self mentored web enthusiast who likes to inspect element everything I see on the web.