Re: Finding users who have dynamically set SORT_AREA_SIZE

Thanks for Jonathan's greate idea. The way to do it is: Find the shadow process pid for the session. Go to svrmgrl (or sqlplus in new version).
oradebug setospid <pid> --You can use other ways to set its pid oradebug dump global_area 14
exit
Look at the trace file in udump. The following line

stpdef stsustp_p [1A39218, 1A39240) = 000F4240 00010000 00000002 0000001B ...

says the sort_area_size of that session is F4240 bytes (i.e. 1000000 bytes).

[The above test is from 8.1.7.2]

There may be other dump types that return this information.

Yong Huang
yong321_at_yahoo.com

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1020865132.12638.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
> Obviously you could do a use oradebug,
> attach to another session and dump its
> global_area, but I don't think that's what
> you would consider an appropriate solution.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Next Seminar - Australia - July/August
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Wed May 08 2002 - 17:51:06 CDT