Tracking Date and Time for Table Data Changes in Oracle

  • Post author:
  • Post category:SQL
  • Reading time:2 mins read

You want to track when changes occur for a particular table in oracle, but your current table structure hasn’t any dedicated fields for this purpose. You want to determine the date and time of historic changes if possible, and provide a mechanism to track future changes.

Use Oracle’s ORA_ROWSCN pseudo-column and the SCN_TO_TIMESTAMP function to find approximate or exact times that rows were changed.

The next SQL illustrates the SCN_TO_TIMESTAMP function using the ORA_ROWSCN pseudo-column to determine the TIMESTAMP value at which rows in the Hr.employees table were changed.

select employee_id, last_name, salary,
scn_to_timestamp(ora_rowscn) Change_Timestamp
from hr.employees;
----------- --------- ------ -------------------------------
210 King 3510 12-SEP-09 PM
211 Sully 3510 12-SEP-09 PM
212 McEwen 3510 12-SEP-09 PM
100 King 24000 08-AUG-09 PM
101 Kochhar 17000 08-AUG-09 PM
102 De Haan 17000 08-AUG-09 PM


Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of and a question and answer forum for developers.