ORA-01536: space quota exceeded for tablespace

create a user with default tablespace set to USERS. Then 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.

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.