find out the last sql statment run associated with a session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: find out the last sql statment run associated with a session

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    How to identify sessions that are not closing?

    I thought by joining v$session and v$sqlarea, I can get the last run sql statment for a session. However, the fact I get 90% of sessions point to the same statement "SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'" makes me wonder if I am doing the right thing.

    The reason I want to do this is because I keep getting max number of processes exceeded error and I see alot of inactive sessions with the above sql statement when joining v$session and v$sqlarea.

    Thanks,
    Last edited by a128; 12-22-2003 at 09:44 PM.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    v$session.PREV_SQL_ADDR

    v$session.PREV_HASH_VALUE

  3. #3
    Join Date
    Apr 2001
    Posts
    257

    How to identify sessions that are not closing?

    Thanks Pando. Even though I found the previously run sql, it still does not solve my real problem:

    Our DB server has been running OK until recently, it exceeded max processes. The front end is Java based app with no JDBC pooling. The DB is in dedicated server mode. The number of (idle) processes seem to accumulate and exceed the max number of processes at an unpredictable pattern. all the connections come from two machines with java apps. I doubt dead connection dection feature will be useful in this case.

    I think it's probably in the java code, some one forgot to close the connection thus let the connection hanging there. Is there a better way to identify this besides by joining v$session and v$sqlarea? I seem to always get: SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT', which doesn't make sense to me in this case.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width