Have you checked MOS for performance issues related to this report (CSTRAIVR)?
I suspect that the process has died and the concurrent manager hasn't picked that up.
Have you checked whether the process is actually still running on the database by looking at active sessions?
Hi Bashar and Ora,
Correction please, the program normally just run 30sec to 10 mins.
What do I do if > the process has died and the concurrent manager hasn't picked that up. Do I need to terminate the request?
How do I create a script that terminates all concurrent request in this status?
How do I check whether the process is actually still running on the database by looking at active sessions?
Can you help how what key-word to look for?
If you session is not showing any activity in v$session from backend then yes you will have to terminate the programs and restart.
Best way to check any such stuck programs is to check backend database session from v$session and look for which sqls the session is running and what is last time ET for that session. If you see the last activity has been few days and if the session is not doing anything then its a stuck/hung/terminated process. You can terminate such sessions.
Check for Last call ET column within v$session, it will tell you when was the last activity performed by that session.
If that is older than few hours or days then session is stuck and not doing any activity. Apart from this you already have the column status which shows inactive. So combination of checking these 2 columns will confirm if a particular session is inactive and not doing any activity.
You can kill those session from database using ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; command.
I am always very careful while cleaning the session. My recommendations would be to check with business user, tell them about the session and take their approval before killing the session. Basically you want to make sure they understand and authorize you for killing the session.
Hope this helps.