Re: Session parameter value of another session

Pratap wrote:
>
> In session 1 I did -
>
> alter session set sort_area_size=100;
> I fired some long running query in session 1.
>
> Now from session 2 can I find out what value of sort_area_size has
> been set for session 1? Using v$sql is one way. Is there any another
> way of finding this out?
>
> Regards,
>
> Pratap

Hi, Pratap,

This question was asked before. Jonathan Lewis suggested dumping global_area of the victim session. Here's the procedure. Find session 1's oracle pid (v$process.pid). In your session 2, type

oradebug setorapid <orapid of session 1> oradebug dump global_area 4

The trace file in udump should have a line that looks like

stpdef stsustp_p [39EE44, 39EE74) = 0012D687 [actual sort_area_retained size here]...

In my test, I set sort_area_size = 1234567 in my session 1, which is 0x12D687.

Level 4 in "dump global_area" command is for UGA, where sort_area_size is allocated. On OSes other than Windows, you can also use other ways (setospid e.g.) to attach to the victim session.

The above works in 8.1.7 and 9.0.1, and possibly other versions.

For other parameters private to a session, you have to do experiments to find the variable/struct name to look for in the global_area dump file.

Yong Huang Received on Wed Dec 22 2004 - 15:42:20 CST