I have a table like the following in oracle:
SELECT * FROM ESTUDIANTES;
ID NOMBRE EDAD
-- ------ ----
1 Pepe 23
2 Maria 35
...
Can I find out in some way what date the insert was made in that table?
I have a table like the following in oracle:
SELECT * FROM ESTUDIANTES;
ID NOMBRE EDAD
-- ------ ----
1 Pepe 23
2 Maria 35
...
Can I find out in some way what date the insert was made in that table?
The answer is NO . If you want the INSERT date, you have to add a date column to the definition of the table for that purpose and keep this information yourself.
In another answer it is suggested to use the special pseudo-column ORA_ROW_SCN combined with the function SCN_TO_TIMESTAMP . The idea is interesting, but it has 2 important problems:
ORA_ROW_SCN returns a number that is the system change number (SCN), which represents the last time you changed the record . That is, every time a UPDATE modifies a record, the ORA_ROW_SCN changes, so the information is lost when the INSERT occurred. SCN_TO_TIMESTAMP to convert the value ORA_ROWSCN to a date will not work and it will give you an error (< em> no snapshot found based on specified time ). This is because the date information is preserved in the Oracle REDO, which is a file that is continually rewritten, and does not save the information permanently. For these 2 reasons, avoid using ORA_ROWSCN + SCN_TO_TIMESTAMP , because even if it seems to work at first sight, if you make a UPDATE to the records or if you wait for a long time, you will see that it will not work anymore.
Use ORA_ROWSCN
SELECT ORA_ROWSCN, ID, NOMBRE, EDAD FROM ESTUDIANTES;
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN), ID, NOMBRE, EDAD FROM ESTUDIANTES;