RE: Quota on TEMP
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <sharmakdeep_oracle@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 21 Sep 2006 09:35:26 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Deepak Sharma
Sent: Wednesday, September 20, 2006 11:32 AM
To: oracle-l@xxxxxxxxxxxxx
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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
