Long Running SQL Sessions using CASE

From: BN <bnsarma_at_gmail.com>
Date: Mon, 18 Sep 2006 08:54:45 -0400
Message-ID: <61292a9d0609180554w493d877cxeed7d9b0ef6c371d@mail.gmail.com>

Greetings,

Oralce 8.1.7.4, HP-UX.

We need to reportt and terminate Long running sessions based on the following requirements:

7 am to 12 (noon) kill RPT_USR* long runners > 2hrs   12:01 to 6:59 kill RPT_USR* long runners > 3 hrs

Here is my SQL For this, which is part of cron scheduled to run every 30 minutes.
The dynamically generated scipt is run part of the K-shell script every 30 minutes.
It sends an email and uses "kill SPID" tp terminate the Long Runners.

right now I comparing the time as shown in the sql script below using case. Is this the right way or
should I convert the time to seconds to compare.

select '
select c.spid, a.username, a.sid, a.serial#,sysdate, to_char(sysdate-a.last_call_et/24/60/60,''hh24:mi:ss'') "Started", b.sql_text
from v\$session a, v\$sqlarea b, v\$process c where a.sql_address = b.address

and a.sql_hash_value = b.hash_value
and a.paddr = c.addr
and a.status = ''ACTIVE''
and a.username like ''RPT_USR%''

'||

(case when to_number(to_char(sysdate,'HH24MISS')) between  7000 and 120000
then '  and a.last_call_et >(120*600)'
      when to_number(to_char(sysdate,'HH24MISS')) between  00000 and 065959
then '  and a.last_call_et >(180*60)'
      when to_number(to_char(sysdate,'HH24MISS')) > 120001  then '  and

a.last_call_et >(180*60)' end) ||' order by 1;' from dual
/

Thank you in advance.

-- 
Regards & Thanks
BN

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 18 2006 - 07:54:45 CDT