1. Is it possible to find the end user name, who logged in ?
2. Check from fnd_users and ping or check with them since from lock view perspective session is active.
- Pavan Kumar N
You've not shown us anything to suggest these sessions aren't being used. Have a look at the last_call_et column of v$session. But even then you have no reason to kill a session unless its actually causing problems.
Are other sessions waiting for them to release locks? Are users phoning you because they can't do their processes?
Yes one OS spid can support more than one session.
Seems you're running E-business suite (at least from looking at the schema names).
I would recommend you ask this question in that forum instead - maybe some "peers" have the same issues as you? E-Business Suite
What queries are you running? For example, you claim that SPID is a column in v$session. It isn't.
Most of your sessions are just the concurrent managers, you can kill them if you want but they will restart when needed. What's the problem?
Thanks Srini and ALL,
See you & Hussein in the Oracle Openworld next week
Timeout setting is default ( i think it is 30 minutes or 1 hour)
So those programs above which are logged-in more that 4 days are not an issue? I thought they are already running for a long time and does not finish? I thought they are users login and left hanging.
We have lots of CPU so we do not felt the cpu stress, but we do encounter issues like users can not login anymore do to limits in OS "can not fork too many process" at the database server.
All our users are reporting 9am to 6pm. They usually click "X" their login forms(abnormal exit) once ready to go home.
So what accounted these more than 4 days processes? and some even 11 days? Maybe the non-graceful exit of users?
Or is it normal for the above programs to be running more than 11 days?
Re: Most of your sessions are just the concurrent managers, you can kill them if you want but they will restart when needed. What's the problem?
The problem is (based on the lock query) we can see lots of similar SPID, if we kill them will other programs which are good, get killed as well?
I am not aware also that they are concurrent programs
So why is the the concurrent programs staying too long? when the reports is supposedly runs only 10 minutes?
Or it is not the program that is running 11 days but only the managers? and Is is not ok to kill it? I am confused
You need to read up (yes, I know you have an aversion to reading) on concurrent managers. They run all the time.
To repeat: what queries did you run to get tat output? Why do you think there is a problem?
The problem is there is increasing number of locking, and it will eventually lead to can not fork too many process at OS level.
Then eventually leads to new users can not login to the EBS sytem
The query run which I got searching in google is: (Note, I did not display some of the columns which include "alter system kill session" which is part of the output columns)
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400),
'SSSSS'), 'HH24:MI:SS') LOGON,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr AND
v$session.status = 'INACTIVE' AND
v$session.username = 'APPS' AND v$session.last_call_et/3600 > 96
AND v$session.action LIKE 'FRM%' -- << inadvertently ommitted
ORDER BY logon_time ASC;