I have a table with a column (CREATED_ON) with type DATE. I insert a row into that today with the value 04-09-2017 12:33:43. However, after 96 hours, if I query for the row, the value of CREATED_ON changes to 04-09-2017 00:00:00.
We scanned our code thoroughly for any bugs that might cause this change while updating records and so on, but could not find any. Unfortunately I do not have any live example of this. Has anyone ever come across this issue? Please help me out.
Thanks,
Sriram Sridharan.
Values in an Oracle Database do not usually change by themselves unless you have a very volatile disk :)
Some things to check:
1. Make sure the values you see after the insert and after the read 96 hours later are read from the same table cell and that the values read are not a result of explicit or implicit data type conversions with the time part being added during the conversion (instead of being read from the disk). You may use the DUMP function to read the actual storage bytes from the table:
SELECT DUMP(my_column,16) FROM my_table WHERE my_primary_key_column = '<known value>'
2. Check for any triggers that might modify the problematic column.
3. Check for any DBMS_JOB jobs that might modify the problematic column.
4. Check for any DBMS_SCHEDULER jobs that might modify the problematic column.
5. Create an ON UPDATE trigger to watch for changes to the column. Use the V$SESSION and V$SESSION_CONNECT_INFO views to extract information about application, host, user, etc. when the trigger fires and store the information in an auxiliary table. This may help with finding out what modifies the value.