ORA-01536: SPACE quota exceeded for tablespace

ORA-01536: space quota exceeded for tablespace is a common error in Oracle Database.
It means that users is trying to use more space than the Database Administrator assigned to him. Quota is a maximum number of bytes that a user can allocate in particular tablespace. Implementing quotas in database is a reasonable approach because it prevents buggy or malicious code to fill the tablespace.


It is worth to mentioned that prior to Oracle 11g quotas in Oracle could be assigned to both permanent and temporary tablespaces. Starting from Oracle 11g quotas can only be set on permanent tablespaces.
Database Administrators should remember that after they setup quotas for users they are responsible for constantly monitoring and extending quotes if required.


Below example presents how we can generate ORA-01535 in Oracle database, and how we can resolve it.

We start from creating a user with default tablespace set to USERS. Then we grant create session and create table privileges, to enable the user to connect to database and create a table. We also grant initially 1 MB quota on tablspace users.





SQL> conn / as sysdba

Connected.

SQL> set linesize 160

SQL> create user user1 default tablespace users quota 1M on users temporary tablespace temp identified by pass1

2 /



User created.



SQL> grant create session, create table to user1

2 /



Grant succeeded




Then we try to create a table using CTAS (create table as select) based on ALL_OBJECTS view. ALL_OBJECTS view
contains a list of ALL_OBJECTS in database.


SQL> conn user1/pass1

Connected.

SQL> set linesize 160

SQL> create table t1 as select * from all_objects

2 /

create table t1 as select * from all_objects

*

ERROR at line 1:

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



The error occurred while we tried to create the table, due to the fact that table t1 would require around 7MB of space and user1 quote was set to just 1MB.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 1





To resolve the problem we enlarge user1 quota to 10MB.

SQL> alter user user1 quota 10m on users

2 /



User altered.



SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /



Table created.

As we can see the table was created successfully. So the last thing to do is to check the quota usage.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10



It is worth to remember that instead of granting quota in megabytes, we can grant UNLIMITED QUOTA on tablespace for
a user. In my opinion it sometimes be a security whole, but on other ocasions it can be useful.

So let's start from dropping the table t1.

SQL> conn user1/pass1

Connected.

SQL> drop table t1

2 /



Table dropped.



SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 7 10



SQL> show parameter recyclebin



NAME TYPE VALUE

------------------------------------ ----------- --------

recyclebin string on



After table drop you see that space used by the user has not been change.
You probably wondering why, the reson is simple, I used the statement DROP TABLE tableName; instead of DROP TABLE tableName PURGE; and I have RECYCLE_BIN ON. Below query allows be to confirm that my table is in RECYCLEBIN.

SQL> set linesize 160

SQL> column owner format a15

SQL> column original_name format a20

SQL> column can_purge format a10

SQL> column type format a10

SQL> select owner, object_name, original_name, operation, type, can_purge

2 from dba_recyclebin

3 where owner = 'USER1' and type = 'TABLE'

4 /



OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
USER1 BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES



To purge the table from recyclebin we will login as user1 and execute PURGE TABLE statement.

SQL> conn user1/pass1

Connected.



SQL>select object_name, original_name, operation, type, can_purge

1 from user_recyclebin
2 where type = 'TABLE'

3 /



OBJECT_NAME ORIGINAL_NAME OPERATION TYPE CAN_PURGE
BIN$2JQWhCy4RIiMi4/epuYlSQ==$0 T1 DROP TABLE YES

SQL> purge table "BIN$2JQWhCy4RIiMi4/epuYlSQ==$0"

2 /



Table purged.

Confirmation of successful purge operation.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10

Please note that above behavior with RECYCLEBIN will only work is instance parameter recyclebin is set to on, and if you are working on Oracle 10g or later. The above experiment was performed on Oracle 11g.

We can now replay our initial test and check that UNLIMITED QUOTA WORKS.

SQL> conn / as sysdba

Connected.

SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB", max_bytes / 1024 / 1024 as "Max MB"

2 from dba_ts_quotas

3 where tablespace_name = 'USERS' and username = 'USER1'

4 /




TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 10

SQL> alter user user1 quota 1M on users

2 /



User altered.



SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /

create table t1 as select * from all_objects

*

ERROR at line 1:

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



SQL> conn / as sysdba

Connected.

SQL> alter user user1 quota unlimited on users

2 /



User altered.

Unlimited Quota is denoted by -1 in MAX_BYTES column of DBA_TS_QUOTAS view.


SQL> select tablespace_name, username, bytes / 1024 / 1024 "Used MB",
2 decode(max_bytes,-1,'UNLIMITED',max_bytes / 1024 / 1024) as "Max MB"

3 from dba_ts_quotas

4 where tablespace_name = 'USERS' and username = 'USER1'

5 /



TABLESPACE_NAME USERNAME Used MB Max MB
USERS USER1 0 UNLIMITED

Finally we can run our CREATE TABLE statement.

SQL> conn user1/pass1

Connected.

SQL> create table t1 as select * from all_objects

2 /



Table created.