Not long ago, I mentioned about how to get approximate commit time on a table: http://mustafakalayci.me/2019/05/25/commit-time-for-an-inserted-row-in-oracle/
While I was reading some blogs and jumping from one to another, I came to Saibabu Devabhaktuni’n blog post: http://sai-oracle.blogspot.com/2013/09/how-to-setup-oracle-on-commit-trigger.html
and I was literally shocked because I never heard something about this. So I did my tests and wanted to share.
So there is an USERENV context parameter called COMMITSCN which works in a very different way.
for test case, I created 2 tables and 1 trigger. trigger is on T1 table and after insert trigger insert into T2 table with commitscn value. After inserting into T1 table, I simply select T2 table with SCN_TO_TIMESTAMP function to see the data as timestamp. and insert time is shown in there. Time is 19.48.49. so far so good. then I waited for a few seconds (I run dbms_lock.sleep function to wait, you can simply wait) and then issue a commit statement and select the T2 table again and result has been changed to 19.48.55. 6 seconds (almost 5) difference. so even we did not update T2 table in any way, Oracle did and after issuing commit, Oracle updated T2 because it is tracking “commitscn” value.
This is an another way to get the commit time of a row. if you get a trace for your current session. you will see that update statement which runs just after the commit statement
interestingly this update runs after the commit statements so you can think that is this update also should be commit? of course No! this update statement is a part of commit process and committed with the commit command that you run previously. Also I saw that oracle is using rowid to update the table. That make me wonder what would be the performance of a multiple row insert at a time and run this:
that makes sense. you can only get commitscn value once per transaction. so instead of “FOR EACH ROW” trigger, we must use this value in a statement trigger. commitscn value will be same for all rows in that transaction (even for the changed rows on different tables).
So in the same transaction if you change data on multiple tables and you want to add a commitscn value for them you can not use same trigger code because after the first one completed others will get ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction error.
Unfortunately it is not easy to use commitscn. it can be used only within a INSERT statement or UPDATE statement. so in a trigger you can not use a command like “:new.commit_scn = userenv(‘commitscn’)”. you can use it in your top level insert stament:
but this will be a problem if you insert multiple rows in a single insert (insert into … select). you can not read commitscn more than once per transaction and it can not be used in a select statement. also in a transaction if you are planning to run insert/update/delete on more than one table, again it will be a problem. trying to use commitscn context parameter will much more harder than you think. let’s say you use it in your first insert statement and and select that value and use it on other tables but Oracle will only track wherever userenv(‘commitscn’) is used not the value of it. so after the commit only the first table’s commitscn value will be updated and not the others.
So implementation I come up with is using a global temporary table. since you can use it once per transaction and this is a session specific value, we can store the commitscn value into a GTT and then you can use committed value anywhere you want.
so you must sure that before your transaction Global Temporary Table (GTT) is empty and truncate table after your transaction is completed.
Besides those, commitscn is not documented, only error definition is documented. so Using it is not much feasible and logical (also while we have rowdependencies ) but who knows maybe we needed it at some point.