Re: Query does not finish

From: Eriovaldo Andrietta <>
Date: Fri, 18 Apr 2014 16:57:58 -0300
Message-ID: <>

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.

2014-04-18 13:43 GMT-03:00 Tim Gorman <>:

> 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
> 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

Received on Fri Apr 18 2014 - 21:57:58 CEST

Original text of this message