Pages

Tuesday, September 3, 2013

ORA-01536: space quota exceeded for tablespace xyz

Today I had this error:

ORA-01536: space quota exceeded for tablespace

So it seems that my user couldn't write to his default tablespace. So deceided to check my user:

SQL> select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username like '%KAREL%';
no rows selected


The above query and result shows me that my user hasn't any quota set. To set one I used the following SQL:

SQL> alter user KARELLEN quota unlimited on XYZ;
SQL> select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username like '%KAREL%';

TABLESPACE_NAME USERNAME BYTES      MAX_BYTES
--------------- -------- ---------- ----------
XYZ             KARELLEN 1151205376         -1


After setting the quota to unlimited (a little oversized i know) everything went back to normal.