ORA-01536

Oracle 10.1.0.2.0
Windows Xp
Archive Log Disable


Suddenly We get ORA-01536 error.But our tablespace already have 12 GB Free Space.

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

Suggestion :

1.You have to first check your tablespace have sufficient space.

select sum(bytes/1024/1024)
from dba_free_space
where tablespace_name = '&tbs_name';

2.Second check user have sufficient space on TABLESPACE.

select sum(bytes/1024/1024),tablespace_name
from dba_ts_quotas
where username = '&USERNAME'
group by tablespace_name;

3.Sometime happen due to "There may be a dependency object on this table.
Insert on this object may need to update the dependant object, which really exhausted the quota."

A.Find for any dependant object over that table.

select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';

B.If found, find the owner of that object.

select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependant object name';

C.Grant unlimited tablespace to that user.

grant unlimited tablespace to [dependant object owner name];


4.If all above point is not help then just revoke "unlimited tablespace" from user and grant "unlimited tablespace" to user again. and try your operation.