Home / Questions / 124730
Accepted
nachfren
Asked: 2020-12-15 03:13:37 +0800 CST 2020-12-15 03:13:37 +0800 CST

Does Oracle 11g save the date of the inserts made?

  • 772

I have a table like the following in oracle:

SELECT * FROM ESTUDIANTES;

ID   NOMBRE   EDAD
--   ------   ----
1    Pepe     23
2    Maria    35
...

Can I somehow find out what date the inserts were made in that table?

sql

2 Answers

  1. Best Answer

    The answer is NO . If you want the INSERT date, you have to add a date column to the table definition for that purpose and maintain this information yourself.

    In another answer it is suggested to use the special pseudo-column ORA_ROW_SCNcombined with the SCN_TO_TIMESTAMP. The idea is interesting, but it has 2 major problems:

    1. ORA_ROW_SCNreturns a number that is the system change number (SCN), which represents the last time the record changed . In other words, every time UPDATEa record is modified, ORA_ROW_SCNit changes, so that the information of when the occurred is lost INSERT.
    2. Even if it doesn't change the record after being inserted, after an indeterminate amount of time, trying to use it SCN_TO_TIMESTAMPto convert the value ORA_ROWSCNto a date won't work and will give you an error ( no snapshot found based on specified time ). This is because the date information is kept in Oracle's REDO, which is a file that is continually rewritten, and does not store the information permanently.

    For these 2 reasons, avoid using ORA_ROWSCN+ SCN_TO_TIMESTAMP, because even if it seems to work at first glance, if you un UPDATEto the records or wait long enough, you will see that it will no longer work.

    • 6

  2. Use ORA_ROWSCN

    SELECT ORA_ROWSCN, ID, NOMBRE, EDAD FROM ESTUDIANTES;

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), ID, NOMBRE, EDAD FROM ESTUDIANTES;
    • 2