Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 17, 2017 - 7:57 am UTC

Last updated: January 18, 2017 - 3:07 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi,


An Application has been sending insert statements to DB continuously from past two days.


I would like to know the time gap between the insert statements.



Thanks & Regards
Krishna.

and Connor said...

If you dont have the timestamp on the rows being inserted, you can take a look at a flashback query to get a reasonable estimate.

SQL> SELECT deptno, dname,
  2       VERSIONS_STARTTIME
  3      ,VERSIONS_XID
  4      ,VERSIONS_OPERATION
  5  FROM dept
  6  VERSIONS BETWEEN
  7     TIMESTAMP SYSTIMESTAMP - INTERVAL '20:00' MINUTE TO SECOND
  8     AND SYSTIMESTAMP
  9  WHERE deptno = 10;

    DEPTNO DNAME          VERSIONS_STARTTIME       VERSIONS_XID     V
---------- -------------- ------------------------ ---------------- -
        10 UNKNOWN        03-SEP-08 11.53.45 PM    0200100060040000 U
        10 MONEY GRABBERS 03-SEP-08 11.53.36 PM    0600050065040000 U
        10 FINANCE        03-SEP-08 11.53.24 PM    09000D001D050000 U
        10 BEAN COUNTERS  03-SEP-08 11.53.12 PM    01001A00EA030000 U
        10 ACCOUNTING


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Interpret

A reader, January 18, 2017 - 8:02 am UTC

Hi Connor
Could please comment in common English row by row the above.
Chris Saxon
January 18, 2017 - 3:07 pm UTC

Flashback query enables you to see how the table looked in the past. Or the table changes between two time periods. 

VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '20:00' MINUTE TO SECOND
AND SYSTIMESTAMP


Finds all the changes to the table in the past 20 minutes. The versions* columns are pseudocolumns showing:

VERSIONS_STARTTIME - when this change happened
VERSIONS_XID - the transaction that made the change
VERSIONS_OPERATION - If this was an I(nsert), U(pdate) or D(elete)

You can read more about this at:

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS1008
https://oracle-base.com/articles/10g/flashback-10g#flashback_query
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.