ORA-01536: space quota exceeded for tablespace ‘USERS’

Hi,

Sometimes you can get ” ORA-01536: space quota exceeded for tablespace ‘USERS ” error.

If you don’t know what is the tablespace, you can read the following post to learn it detailed.

https://ittutorial.org/what-is-the-tablespace-in-oracle-how-to-add-datafile-a-tablespace-and-tablespaces-usage-size-check/

Details of error are as follows.

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

Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.

Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner.

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

ALTER USER <username> QUOTA 1024M ON <tablespace name>;

SQL> alter user MSDEVECI quota 1024M on users;

User altered.

ALTER USER <user> quota unlimited on <tablespace name>;

SQL> alter user MSDEVECI quota unlimited on USERS;

User altered.

Check user’s quota on related tablespace.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", 2 decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"
 from dba_ts_quotas where tablespace_name = 'USERS' and username = 'MSDEVECI';

GRANT UNLIMITED TABLESPACE TO <username>;

SQL> GRANT UNLIMITED TABLESPACE TO MSDEVECI;

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/