Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Oracle .

I need to identify all "alter session" changed for a specific session. There is some v$session_* where I able to get this information?

My objective is part of investigation what I doing about a problem of sessions hanging at compile time... from Forms Builder.

I already got a trace of the session and have some suspicious over a bug documented at Oracle Support... but I want more details about this session.

share|improve this question
The only way of doing this is using session traces, as the relevant session information isn't exposed to other sessions. This is essentially the same as this question dba.stackexchange.com/questions/24456/… –  FreshPhilOfSO Oct 31 '13 at 16:21
I doubt a session trace will get you those. AFAIK session level parameters are kept in PGA, so you will have connect to the spid and dump PGA and investigate. It is easier said than done. If you are interested in optimizer related parameters then you are in luck, you could try v$ses_optimizer_env. –  Raj Oct 31 '13 at 17:22
Thanks Phil and Raj , I looking for something more ""online". The trigger will not work for me because if something is changed during the session I will not get. Dump PGA at this moment I consider "out of question" ... I'm not looking for optimizer, I need to check for any unusual setting where can affect the hang problem. –  ceinmart Nov 1 '13 at 14:30
Actually, you could create a logoff trigger at database level and capture only those parameters that have non-default values per session. This will get you the parameters and values at the logoff time, but if it changes in-between you can't unless you take a PGA dump. If you revoke 'alter session' from your users, they will not be able to change the parameters anyway. also what makes you think those parameters are causing the hang? it has been many years since i worked with forms, but make sure all database links are valid and working. if one of them isn't, you could feel the 'hang'. –  Raj Nov 1 '13 at 15:23
Hi @Raj, I think our problem maybe the old version of forms (6i) accessing v11.2.0.3 . I have the suspicious we are stopping at the Bug 16718027 (diana_version$ access). I have a SR open at oracle support trying find the cause. We have some dblinks, I will re-check them... thanks! –  ceinmart Nov 1 '13 at 16:13
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.