Long Running SQL Sessions using CASE
- From: BN <bnsarma@xxxxxxxxx>
- To: _oracle_L_list <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Sep 2006 08:54:45 -0400
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
Other related posts:
- » Long Running SQL Sessions using CASE
