Quota on TEMP

  • From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 20 Sep 2006 09:32:27 -0700 (PDT)

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.


Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 

Other related posts: