Re: Setting sort_area_size for another session

In article <c4fbc557.0111261028.115bbf7f_at_posting.google.com>, lundellj_at_saic.com says...
>
>I know I can set sort_area_size (and sort_area_retained_size) at the
>instance level in init.ora and for "my" session using "alter session
>set sort_area_size = xxx". But is it possible to set sort_area_size
>for another session or for a specific user and/or user type? We have a
>third party application that (due to bad SQL) needs a BIG sort area
>but we can't afford to set that big sort area at the instance level.
>
>Thanks,
>
>-Jens

Well, since sort_area_size is more of an upper bound then a fixed size of memory....

and we only allocate sort memory when we sort (so unless they need it, they won't allocate it) ....

... if the other sessions don't need that much, they won't use that much!

But, you can try a logon trigger with:

begin
  execute immediate 'alter session set sort_area_size = .....'; end;

and either do it "on schema" for each user you want to have this or code:

   if ( user in ('BOB', 'MARY', .... ) )    then

       execute immediate ...
   end if;

(of course the logon trigger and execute immediate are both part of Oracle8i release 8.1 and up, not in 8.0 and before but there isn't any version to work with here!)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp