0

Background: I have an oracle table, the table doesn't have any specific column as timestamp while table creation script. This table can have millions of rows.

Example: 

Employee {Emp_No, Name, Manager,Division, Role, Region}

My quest: If any updates happened through a job on that table, can i know which all rows got updated. Does oracle have any internal timestamp for each row which i can leverage. Can i use it in query to get all records.

Reason: I need to show my team those ambiguous records weren't updated by the job we all are suspecting.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Rohan K
  • 177
  • 1
  • 3
  • 21
  • Why not add a nullable timestamp column and have the job populate it on update? That should be proof enough. – Guillaume CR Mar 24 '17 at 15:14
  • Can't add, it's a production table. Changes can not be done easily. – Rohan K Mar 24 '17 at 15:16
  • Does [`ora_rowscn`](https://docs.oracle.com/database/121/SQLRF/pseudocolumns007.htm#SQLRF50953) give you the info you need, if this is a one-off task? Not clear if you need it to be more definitive than that allows... it may show the rows you're interested *weren't* updated by a recent SCN. – Alex Poole Mar 24 '17 at 15:17

2 Answers2

2

Oracle has ORA_ROWSCN Pseudocolumn. This columns returns "the conservative upper bound system change number (SCN)" of last transaction made on row or data block. This is a good estimate for when the block or row was last changed.

If your table is create with ROWDEPENDENCIES, ORA_ROWSCN returns scn for row. NOROWDEPENDENCIES is the default, in which case Oracle tracks SCN at the block level.

SCN_TO_TIMESTAMP allows you to converto scn to timestamp but for old scn it raises exception.

APC
  • 144,005
  • 19
  • 170
  • 281
Arkadiusz Łukasiewicz
  • 6,241
  • 1
  • 11
  • 17
1

Approximate update time can be retrieved with SCN_TO_TIMESTAMP(ORA_ROWSCN)

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm

Serg
  • 22,285
  • 5
  • 21
  • 48
  • The `scn_to_timestamp` call will fail for older rows though (i.e. probably those not updated here); possibly better to get the SCN for a time just before the update, if that is still available, and look at the `ora_rowscn` directly? I guess if you're looking at a specific row then an exception would tell you something anyway. – Alex Poole Mar 24 '17 at 15:21
  • Excellent. Learning something new everyday. I didn't know either this pseudocolumn existed nor the timestamp conversion function. – unleashed Mar 24 '17 at 18:37