Hello Tim, I will check the SQLNET.EXPIRE_TIME and another new information for me is : the process and sockets from the pooled connection from JBoss still exist In the query i will use the gv$SESSION because the environment is a RAC. I think this is the way to justify "why" and "what need" to kill the session. Thanks a lot for your answer. Eriovaldo 2014-04-18 13:43 GMT-03:00 Tim Gorman <tim@xxxxxxxxx>: > Eriovaldo, > > From what you describe, it is likely that the sessions are not "running" > but are probably STATUS='INACTIVE' and sitting waiting for JBoss to say > something by waiting on the event "SQL*Net message from client". > > You should check out the functionality surrounding the SQL*Net parameter > SQLNET.EXPIRE_TIME and find out why that doesn't seem to be working, but > chances are good that it isn't working is because the process and sockets > from the pooled connection from JBoss still exist, so SQL*Net still thinks > everything is OK at the network layer. > > So, in the meantime, how about running something like this a couple times > daily and emailing yourself the resulting output... > > SELECT 'alter system kill session '''||sid||','||serial#||''' /* > '||username||', '||program||' */ ;' cmd > FROM V$SESSION > WHERE STATUS <> 'ACTIVE' > AND TYPE = 'USER' > AND EVENT = 'SQL*Net message from client' > AND SECONDS_IN_WAIT > 86400 /* 1 day */ ; > > > You can add more inclusive or exclusive filters on columns like USERNAME, > MACHINE, etc. > > Having the generated commands just emailed to you initially allows you to > determine how effective (or dangerous) this "sniper" might be, providing > the opportunity to check things out before running the ALTER SYSTEM KILL > command, to ensure you're not killing something that shouldn't be killed. > After a while, when you're confident, you can have the script just run the > ALTER SYSTEM KILL command automatically and then have it email you the > output. > > Or, just kill 'em all and let God sort 'em out. :-) > > Hope this helps... > > -Tim > > > On 4/18/2014 8:00 AM, Eriovaldo Andrietta wrote: > > Hello, > > I would like to know why does it happen: > > I have a scheduled process in Jboss, that calls a java application and it > sends a SQL to the database. > > All threads from JBoss were canceled and the SQL is still running. If we > does not kill it, it stays there running for a week and does not finish. > > Note : If we run the SQL manually , via SQLPLUS, it runs fully, return > data and complete the execution. > > My doubt is : > > How can I indentify , in the database, looking to the v$session, v$sqlarea > and others views and get some information for making decision (for sure) > in order to Kill de session, > It spend a lot of database resource and cannot keep executing for a week. > > I imagine that the session cannot give a return to the listner , because > the Jboss were canceled. > > Does anyone have a query to identify it. > > Regards > Eriovaldo > > >