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
Login to your oracle server and connect to sqlplus
$ sqlplus / as sysdba
Output: SQL*Plus: Release 188.8.131.52.0 - Production on Mon Aug 2 20:27:54 2021 Version 184.108.40.206.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.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>