Send mail when transaction time is behind system time on UNIX

I have a query to run on oracle that gives output as sid,serial#,transaction start time,sql_id,transaction id.

What I need to do is, whenever a transaction's start time is more than 1 hour behind the system time, I need to run another query with that sql_id and send it as an email.

How do I compare this time output from ORACLE sql and compare it with the system time?

I need to automate this p开发者_运维技巧rocess and add it to the cron on UNIX.

Please help!

The function SYSDATE returns the current system date. When you subtract two dates, you get a difference measured in days. Multiply by 24 and you get a difference in terms of hours.

SELECT *
  FROM v$transaction
 WHERE (sysdate - start_date)*24 > 1

will give you the transactions that started more than 1 hour ago. You can also use interval arithmetic if you find that clearer.

SELECT *
  FROM v$transaction
 WHERE sysdate - interval '1' hour > start_date