Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 26 May 2012

ORA 1536: space quota exceeded for tables

Symptoms: Insert on a table reports, ORA-01536 but still enough space is available in that tablespace. I also granted unlimited tablespace to username but still faced the same issue.
Cause: This exception occurs when the maximum space allocated for a table is reached and the attempt to allocate more space fails. There may be a dependency object on this table. Insert on this object may need to update the dependent object, which really exhausted the quota. But the error will be reported generally.
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.
How to Fix it:
1.Find for any dependant object over that table.
select NAME,TYPE from dba_dependencies where REFERENCED_NAME=’table name’;
2.If found, find the owner of that object.
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME=’dependant object name’;
3.Grant unlimited tablespace to that user.
grant unlimited tablespace to ;
Try now you will be able to do the insertion over that table.

Additional Information:
UNLIMITED TABLESPACE is kind of "wierd" system privilege. It is not granted to any role (and in fact it CAN NOT BE GRANTED to any role!), yet when you grant some powerfull roles (like DBA and RESOURCE to user, Oracle adds that UNLIMITED TABLESPACE privilege to that user directly. And when you revoke such role from user, Oracle silently revokes UNLIMITED TABLESPACE privilege from that user.
Now, when someone gets UNLIMITED TABLESPACE granted, it doesn't mean that quotas on all tablespaces are set to unlimited to him. His quotas on tablespaces remain exactly the same - if he had quota 0 on tablespace USERS before, he still has quota 0 on that tablespace now. But when he has UNLIMITED TABLESPACE privilege, this takes priority over any quota settings and therefore he will be able to use space in that tablespace for new extents. However, if you later revoke UNLIMITED TABLESPACE from that user, his quota 0 will be put in effect and he will no longer be able to use any additional available space from tablespace USERS.


Post a Comment