Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01536
ORA-01536 [message #264325] Mon, 03 September 2007 00:50 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
In an interview, I was asked what can be the cause of ORA-01536: space quota exceeded for tablespace 'string' -- if the user is just running a simple SELECT statement which does not even have aggreagations or any computations. I was quiet. Any idea?
Re: ORA-01536 [message #264327 is a reply to message #264325] Mon, 03 September 2007 00:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ORDER BY clause?
Re: ORA-01536 [message #264332 is a reply to message #264325] Mon, 03 September 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A select use:
* data blocks in read access -> no quota used
* undo blocks in read access -> no quota used
* temp blocks if there is a order, distinct, group by, or any operation requiring a sort (merge, hash, PQ...), this is read write.

Temporary space does not require quota unless user temporary tablespace is not a real one (created with "create temporary tablespace").

(From time to time "select" generates writes due to delayed block cleanout but this does not require quota.)

Regards
Michel
Re: ORA-01536 [message #264363 is a reply to message #264332] Mon, 03 September 2007 02:13 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
It may be due to dependent objects
select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';

It can be solved by granting unlimited space to the owner of the object
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependant object name';

grant unlimited tablespace to <dependant_object_owner_name>;


Regards
Sunil Gaurav
Re: ORA-01536 [message #264369 is a reply to message #264363] Mon, 03 September 2007 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It may be due to dependent objects

Post an example. Unless you call a procedure that makes modifications...
Ah yes, maybe with auditing (fine grained auditing) you may also have this.
Quote:
It can be solved by granting unlimited space to the owner of the object

Never do that.
Do you want someone fill your tablespace SYSTEM?

Regards
Michel
Re: ORA-01536 [message #264469 is a reply to message #264369] Mon, 03 September 2007 08:15 Go to previous messageGo to next message
gauravsunil
Messages: 68
Registered: August 2007
Location: bhubaneswar
Member
yes Michel, I got that through auditing only...I had not implemented that! Now I am getting the mistakes their in that code.
Thanks for your advise ! Smile
Regards
Sunil Gaurav
Re: ORA-01536 [message #264472 is a reply to message #264325] Mon, 03 September 2007 08:21 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
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.
Re: ORA-01536 [message #264509 is a reply to message #264472] Mon, 03 September 2007 10:21 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And how does this answer the question?

Regards
Michel
Previous Topic: Comparing values of two columns in same table
Next Topic: how to populate owa_util.ident_arr and dbms_sql.varchar2_table (merged)
Goto Forum:
  


Current Time: Sun Dec 04 17:00:41 CST 2016

Total time taken to generate the page: 0.05301 seconds