ORA-01536: space quota exceeded for tablespace

Problem Description:
----------------------------
Whenever I try to insert data into a table it failed with error message ORA-01536 as follows.

SQL> create table test2 tablespace TEST_DF_DELETE as select level a1, level a2 from dual connect by level <10000;

ERROR at line 1:

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

Cause of The Problem:
-------------------------
The user has exceeded his quota limit. He attempt to perform an operation which requires the creation of a new extent in a tablespace but it failed and error return. The user failed to create new extent as he has already reached his quota of space in the tablespace.

Solution of The Problem:
----------------------------
At first determine the quota of the user in the specified tablespace.
SQL> select * from user_ts_quotas;

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
TEST_DF_DELETE 196608 204800 24 25 NO

Scenario 1:
---------------
Here we see that he is allowed to use maximum 204800 bytes or 25 blocks. And he already has used 196608 bytes. Now the creation of table test2 requires the bytes more than 204800-196608 and hence error comes.

If you see that there is quota exhausted then increase the amount of quota of the user. To do so log on as a dba user and run ALTER USER like,

SQL> CONN ARJU/A
Connected.

SQL> ALTER USER INDIA QUOTA 1M ON TEST_DF_DELETE;
User altered.

Now connect as that user and execute the operation.

SQL> CONN INDIA/T
Connected.

SQL> create table test2 tablespace TEST_DF_DELETE as select level a1, level a2 from dual connect by level <10000;

Table created.

Scenario 2:
-----------------
There may be the case that user already assign to unlimited quota that is -1 on the tablespace but yet the error comes. In that case there may be a dependency object on this table. Insert on the main object may need to update the dependent object, which really exhausted the quota.

In that case do the following.

i)Find any dependent objects for that table.
select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';

ii)If found, find the owner of that object.
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependent_object_name';

iii)Grant unlimited tablespace to that user.
grant unlimited tablespace to depenent_object_owner_name;

Now you will be able to do the insertion on that table.

Related Documents

ORA-30041: Cannot grant quota on the tablespace

Changing a DBA user to a normal user in oracle

Create user in oracle

Minimum privilege needed to take data pump export

A user can do work in his schema with only Create Session Privilege.