Table Analysis with Execution Log

Question

Table Analysis with Execution Log

Navigation

#1 by (3 votes)

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 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 fed by a C # executable, which runs every 3 minutes. However, it can happen that the executable stops running because the server is unavailable ... and I need to know when that stop occurred .. I confess that I could not think of nothing to be able to solve this problem ... does anyone have any idea that can return something like:

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

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

sql oracle

asked by anonymous 04.04.2016 / 14:43

1 answer

Error: Can not read property 'each' of undefined [closed] Change DialogFragment size according to Android screen

score 3 · Accepted Answer

Question answered in the stackoverflow.com , there is a function of oracle that shows the previous record, the function LAG and LEAD , then the idea is for each record to take the date of the previous record, to make the difference between them and to bring only the records that have a 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 previous and subsequent record date, and "pick up" the difference in minutes always according to the previous record.

Result obtained: