Script to check OpenDB

I am mostly working on automating all the steps of deployment and testing. Most of the time we face open DB issue. Somewhere in the server, there can be code issue which leads to holding database connection. This happens mainly with the old release codes. This needs to be constantly monitored across the system. The better approach would be poling DB to check no of database connection which is idle for more than specified time (say 1 hour). This is simple in case of Oracle, because this information can be derived from v$session table.

It has to be implemented with some scripting language (like PERL), but decided to use ant because this has been used before. Here is the sample target used,

<target name="db1" if="oracle1.url" >
 <!-- Connect to database to find idle connections -->
 <echo message="......................... ${db1.name} .........................."/>
 <sql
     driver="${oracle1.driver}"
     url="${oracle1.url}"
     userid="${oracle1.user}"
     password="${oracle1.password}"
     print="yes"
  showheaders="false"
  showtrailers="false"
  onerror="continue"
  output="${session.log}"
  keepformat="yes"
     >
 <classpath refid="common.classpath"/><![CDATA[
  select     rpad(username,15 ,' ')
      rpad(MACHINE, 30,' ')
   rpad(process,15, ' ')
   lpad(last_call_et,15, ' ') '   '
   rpad(osuser, 15,' ')
  from V$session where
   last_call_et > ${oracle1.min.idle.time}
   and last_call_et < ${oracle1.max.idle.time}
   and username = UPPER('${oracle1.user}')
   and (
    machine in ( ${oracle1.machine.name} ) OR
    osuser in ( ${oracle1.os.user} )
   );
  ]]>
 </sql> 
</target>

All the parameter can be configured and email the result file session.log

Now this can be set as crontab and run every one hour.