Looking after an overnight batch process can be a big fraught at times.
If it’s a good day, you might begin the morning with a nice cup of coffee and a leisurely scroll through the logs to confirm that all is well.
In contrast, if the batch has overrun you may well find yourself scrambling through those same logs whilst gulping down large quantities of caffeine in a desperate attempt to hot-wire your brain into working out the elapsed time between each entry. Not great. Especially when you consider that, as Terry Pratchett put it,
“Coffee is a way of stealing time that should by rights belong to your older self”.
A better approach might be to get Oracle to do it for you.
What we’re going to look at here is :
- the INTERVAL data type that holds the difference between two TIMESTAMP values
- using the LAG SQL function to report the interval time between timestamps across rows of a query
- creating some graphs using SQLDeveloper User Defined Reports for when it’s just too early for words and numbers.