Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vannessa.

Asked: May 16, 2017 - 1:18 pm UTC

Last updated: May 17, 2017 - 8:40 pm UTC

Version: oracle 12c

Viewed 1000+ times

You Asked

Hello,
the purpose of my task is to track the modifications on a table. More precisely i want to know if a cell has been updated or a new row inserted. I know i can use an audit tables to do that but it is not convenient because with an audit table i have a long list with all modifications and it will be complicated to find what i want.
Therefore i want to know if this task is possible : when i selected a cell in the database ( using e.g oracle sql developer) i want to have the possibility to see the history of this cell. E.g: if the value of this cell has been updated i want to see in a contextual menu who changed this value and when and what was the old value.
I want to know if there any add-on for Oracle SQL developer or database tool that do that.

Oder if it is possible to have a database tool or an add-on for Oracle SQL developer that highlights a cell when this has been modified.

and Connor said...

If you want recent history you can do flashback query

SQL> SELECT deptno, dname,
  2         VERSIONS_STARTTIME
  3        ,VERSIONS_XID
  4        ,VERSIONS_OPERATION
  5  FROM dept VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

    DEPTNO DNAME          VERSIONS_STARTTIME       VERSIONS_XID     V
---------- -------------- ------------------------ ---------------- -
        50 UNKNOWN        03-SEP-08 11.08.15 PM    04000700EA030000 U
        30 UNKNOWN        03-SEP-08 11.08.15 PM    04000700EA030000 U
        20 NERDS          03-SEP-08 11.07.57 PM    090016001D050000 U
        20 R&D            03-SEP-08 11.07.48 PM    05000B0074040000 U
        ...



which is limited to your undo retention setting.

Alternatively, you could consider flashback data archive.

Details on that here

https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFFDCEH

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