find out the last sql statment run associated with a session

[a128]

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,

[pando]

v$session.PREV_SQL_ADDR

v$session.PREV_HASH_VALUE

[a128]

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,