Table Analysis with Execution Log

Question

Table Analysis with Execution Log

Asked 8 years, 6 months ago

Viewed 447 times

1

I have a table in Oracle 11g with the following structure:

CREATE TABLE USER.LOG_EXECUCAO
(
  ID                       NUMBER,
  DATA                     DATE,
  QTD_REGISTROS_INSERIDOS  NUMBER,
  TEMPO_EXECUCAO           VARCHAR2(50 BYTE)
)

An example of data:

ID      DATA                QTD_REGISTROS_INSERIDOS TEMPO_EXECUCAO

6356    04/04/2016 09:36:03 1074    00h:00m:58s:769ms
6355    04/04/2016 09:33:00 1074    00h:00m:56s:221ms
6354    04/04/2016 09:30:11 1074    00h:01m:06s:719ms
6353    04/04/2016 09:27:13 1074    00h:01m:08s:977ms
6352    04/04/2016 09:24:13 1074    00h:01m:09s:361ms
6351    04/04/2016 09:21:12 1074    00h:01m:07s:685ms
6350    04/04/2016 09:18:11 1074    00h:01m:06s:657ms
6349    04/04/2016 09:15:01 1074    00h:00m:57s:074ms

This table is powered by a C#executable, which runs every 3 minutes.. But it may happen that the executable stops running due to unavailability of the server... and I need to know when it stopped.. I confess that I could not think of anything to solve this problem... someone has some idea that can return me something like:

Between date X and Y, there was a failure in execution during X minutes. (differs from one date to another)

The idea was to try to solve by SQL.... Suggestions?

Oracle does not have Jobs to do this?

– Marco Souza

2016/04/04 at 12:47
I don’t know, buddy.... I can’t tell you how

– Marllon Nasser

2016/04/04 at 12:50
See this here

– Marco Souza

2016/04/04 at 12:51
I think it’s much better if you keep your logic all in the database if your data came from there. (This table is powered by a C executable#)

– Marco Souza

2016/04/04 at 12:53
Ah, yes... the job itself I know, but it’s impossible... the C# executable does things that through PL/SQL I can’t do, like access certain websites, monitor whether a server is out or not - through ping.

– Marllon Nasser

2016/04/04 at 12:54
@Marllonnasser you could use?

– Tiedt Tech

2016/04/04 at 12:55
@Marlontiedt, I’d like the information to come in real time...?

– Marllon Nasser

2016/04/04 at 12:56
@Marllonnasser puts your script with data in http://sqlfiddle.com/ with ok data and not ok data, I will try to help you

– Tiedt Tech

2016/04/04 at 12:58
thanks, @Marlontiedt! I got an answer and made it available to anyone who wants to do something similar :)

– Marllon Nasser

2016/04/04 at 13:17

Show 4 more comments

1 answer

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.

by Marllon Nasser • **3,845** points · Answer 1 · 2016-04-04T13:17:34+00:00

Question answered in stackoverflow.with, there is an oracle function that shows the previous record, the function LAG and LEAD(in English), then the idea is, for each record, take the date of the previous record, make the difference between them and bring only the records that have difference greater than X minutes:

SELECT *
  FROM (SELECT ID, DATA, LAG (DATA) OVER (ORDER BY DATA) AS data_anterior,
               LEAD (DATA) OVER (ORDER BY DATA) AS data_posterior,
               FLOOR ((DATA - LAG (DATA) OVER (ORDER BY DATA)) * 24 * 60
                     ) AS minutos_diferenca
          FROM LOG_EXECUCAO)
 WHERE minutos_diferenca > 10

In the example above, always getting the date of the previous and later record, and "caught" the difference in minutes always according to the previous record..

Result obtained: