Commit Time for an Inserted Row in Oracle

Hello everyone,

This week, one of my customer have experienced a problem. They provide some Web Service to their suppliers and one of those web services creates some rows and then returns the response as successful operation but just after that, supplier queries the newly created those rows but they get “no rows”! When analyze and development team came to me I simply look at the logs and fortunately they are storing their responses in a table with timestamp values too. After checking some tables, we find out that rows are created first and then responses are sent to caller. Also business logic is in that way. there is no way to return the response before the rows created.

So simply I asked about “commit time” because this is the only things that we can not measure in this case. rows might be created before sending the response but they might not committed yet so caller can not see them when querying with another session. Development team can not answer my question exactly because of very complicated framework logic. Somehow I need to solve this.

Basically I needed the commit time of that rows. while inserting a row you might use “sysdate” to understand when this row inserted but actually this is not enough and accurate because I might insert the row at 22:00:00 and write this date along with the row but might commit it at 22:01:00! Commit is 1 minute later. so when I check the row few hours later, I will “think” that this row is exist at 22:00:25 but actually it is not because it wasn’t committed yet but I don’t know that.

How can we check that? you might heard about “ora_rowscn” if not you can find some details here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ORA_ROWSCN-Pseudocolumn.html#GUID-8071AAB0-F656-4C93-B926-0BCE1439F121

This is a pseudo column which means it is stored without your action in your table. ora_rowscn stores scn (system change number) information and by default table stores this data per your table blocks not per row! that means a bunch of rows has same ora_rowscn value because they are in the same block but you can change this behave and force Oracle to store ora_rowscn per row and this will give you an idea about the “commit time” of every row. let’s demonstrate:

I created a table with “ROWDEPENDENCIES” clause which allows Oracle to store ora_rowscn per row. then I inserted and committed 2 rows but after inserting the second row I am waiting for 10 seconds. when we check the rows they both have same inserted_date but they have different ora_rowscn values and ora_rowscn almost shows the “commit time”.

I said almost because Oracle does not guarantee that. ora_rowscn will show at least the “commit time” not before that but might show a little bit after the exact commit time. even tough, it gave us a clue. in my example I am inserting second row at 22:48:26 but commit has arrived at 22:48:36.

as a con, “row dependency” property can not altered after the creation of the table which means you can not change your existing table you need to create a new table. As you guess my customer’s table doesn’t store ora_rowscn per row so rhat did I do to solve it? Simply created a new table with the Id of base table and with rowdependencies, then added a trigger to base table. Whenever an insert runs against the table, I also inserted it to my new table with the ID of the row and then check the ora_rowscn from new table.

Also a reminder. storing ora_rowscn per row requires more space than per block(by default).

I hope that helps.