Press "Enter" to skip to content

Tracking Date and Time for Table Data Changes in Oracle

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;

EMPLOYEE_ID LAST_NAME SALARY CHANGE_TIMESTAMP
———– ——— —— ——————————-
210 King 3510 12-SEP-09 03.54.55.000000000 PM
211 Sully 3510 12-SEP-09 03.54.55.000000000 PM
212 McEwen 3510 12-SEP-09 03.54.55.000000000 PM
100 King 24000 08-AUG-09 11.06.28.000000000 PM
101 Kochhar 17000 08-AUG-09 11.06.28.000000000 PM
102 De Haan 17000 08-AUG-09 11.06.28.000000000 PM

Vinish Kapoor

FoxInfotech.in is created, written, and maintained by Vinish Kapoor. It is built on WordPress, and hosted by Bluehost. Connect with Vinish on Facebook, Twitter, and GitHub.

You may also like: