| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Quota on TEMP
Warning: The response below contains a lot of opinions. Those who aren't
interested know what to do.
I don't see the benefit of this feature; if the user needs to run the query for a valid business purpose and the query is written well (i.e. not missing a join etc.) then the temp space should be created large enough to accommodate it. If the user doesn't need to run the queries or doesn't know how to write well formed queries then he shouldn't be allowed to write them to begin with, especially in production and a request made to the reporting staff for the data.
Every time a user has run out of temp space in my experience, it is always due to a bad query, typically missing a join or a where clause, the oracle error misleads the user as it suggests more temp space is required when fixing the query is in order and increasing the temp space by 10 fold won't even help if the query is written wrong enough that you ran out temp space in the first place. If the user has a valid need for the data throwing an error by killing their session, or by setting a quota will just make them run it again and again and bug you to increase their quota. Educate them on how to write sql better and you avoid all these issues.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Deepak Sharma
Sent: Wednesday, September 20, 2006 11:32 AM
To: oracle-l_at_freelists.org
Subject: Quota on TEMP
We have certain Users running queries that frequently fill-up the TEMP tablespace. Is there a way to specify quota on TEMP. We are on 10gR2 and I already found that this stopped working in 10gR2 (it was in fact a Bug in earlier versions).
SQL> alter user dummy quota 100m on temp;
alter user dummy quota 100m on temp
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace
But, I still want to use a workaround, if there's one. One way is to create separate Temp for those users, but that would get filled too. Increasing the Temp size is just a band-aid, since our Temp is adequately sized for majority of the users. I looked into Resource Mgr, but there we can set a limit on UNDO, not Temp.
Thanks,
Deepak
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 21 2006 - 09:35:26 CDT
 |
 |