Undocumented

Hello again,

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.

create table t1 (id number);

create table t2 (id number, Commit_scn Number);

create or replace trigger tr_t1 after insert on t1 for each row
begin
  insert into t2 values (:new.id, USERENV('COMMITSCN'));
end;
/

INSERT INTO T1 VALUES (1);

select scn_to_timestamp(commit_scn) from t2;
-----------
--18/12/2019 19.48.49,000000000

exec dbms_lock.sleep(5); -- or just wait for a few seconds

commit;

select scn_to_timestamp(commit_scn) from t2;
-----------
--18/12/2019 19.48.55,000000000

create table t1 (id number);

create table t2 (id number, Commit_scn Number);

create or replace trigger tr_t1 after insert on t1 for each row

begin

insert into t2 values (:new.id, USERENV('COMMITSCN'));

end;

INSERT INTO T1 VALUES (1);

select scn_to_timestamp(commit_scn) from t2;

-----------

--18/12/2019 19.48.49,000000000

exec dbms_lock.sleep(5); -- or just wait for a few seconds

commit;

select scn_to_timestamp(commit_scn) from t2;

-----------

--18/12/2019 19.48.55,000000000

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

UPDATE T2 SET COMMIT_SCN = :1 WHERE rowid = :2

1	UPDATE T2 SET COMMIT_SCN = :1 WHERE rowid = :2

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:

ODB_ADMIN@orcl> insert into t1 select level from dual connect by level <= 10;
insert into t1 select level from dual connect by level <= 10
            *
ERROR at line 1:
ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction
ORA-06512: at "ODB_ADMIN.TR_T1", line 2
ORA-04088: error during execution of trigger 'ODB_ADMIN.TR_T1'

ODB_ADMIN@orcl> insert into t1 select level from dual connect by level <= 10;

insert into t1 select level from dual connect by level <= 10

ERROR at line 1:

ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

ORA-06512: at "ODB_ADMIN.TR_T1", line 2

ORA-04088: error during execution of trigger 'ODB_ADMIN.TR_T1'

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:

drop table t1;
create table t1 (id number, commit_scn number);
insert into t1 values (1,USERENV('COMMITSCN'));

select commit_scn from t1;
---------
--4935733

commit;
select commit_scn from t1;
---------
--4935736

drop table t1;

create table t1 (id number, commit_scn number);

insert into t1 values (1,USERENV('COMMITSCN'));

select commit_scn from t1;

---------

--4935733

commit;

select commit_scn from t1;

---------

--4935736

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.

Create Table T1 (Id Number, Commit_Scn Number);
Create Table Y1 (Id Number, Commit_Scn Number);

Create Global Temporary Table Gtmp_Commit_Scn (Commit_Scn Number) On Commit Preserve Rows;


Create Or Replace Procedure Sp_Store_Commit_Scn As
  X_Exist_Commit_Scn Number;
Begin
  Select Count(*) Into X_Exist_Commit_Scn
  From   Gtmp_Commit_Scn;
  
  If X_Exist_Commit_Scn = 0 Then
    Insert Into Gtmp_Commit_Scn Values (Userenv('commitscn'));
  End If;
End;
/

Create Or Replace Trigger Tr_T1 Before Insert Or Update On T1 For Each Row
Call Sp_Store_Commit_Scn
/

Create Or Replace Trigger Tr_Y1 Before Insert Or Update On Y1 For Each Row
Call Sp_Store_Commit_Scn
/


Insert Into T1 (Id) Values (1);

Select * From Gtmp_Commit_Scn;
-------
--4943666

Insert Into Y1 
  Select Level, Null From Dual Connect By Level < 10;;

Select * From Gtmp_Commit_Scn;
-------
--4943666

Commit;

Select * From Gtmp_Commit_Scn;
-------
--4943671

update t1 set commit_Scn = (Select g.commit_Scn  From Gtmp_Commit_Scn g) /*where ...*/;
update t2 set commit_Scn = (Select g.commit_Scn  From Gtmp_Commit_Scn g) /*where ...*/;

truncate table Gtmp_Commit_Scn ;

Create Table T1 (Id Number, Commit_Scn Number);

Create Table Y1 (Id Number, Commit_Scn Number);

Create Global Temporary Table Gtmp_Commit_Scn (Commit_Scn Number) On Commit Preserve Rows;

Create Or Replace Procedure Sp_Store_Commit_Scn As

X_Exist_Commit_Scn Number;

Begin

Select Count(*) Into X_Exist_Commit_Scn

From Gtmp_Commit_Scn;

If X_Exist_Commit_Scn = 0 Then

Insert Into Gtmp_Commit_Scn Values (Userenv('commitscn'));

End If;

End;

Create Or Replace Trigger Tr_T1 Before Insert Or Update On T1 For Each Row

Call Sp_Store_Commit_Scn

Create Or Replace Trigger Tr_Y1 Before Insert Or Update On Y1 For Each Row

Call Sp_Store_Commit_Scn

Insert Into T1 (Id) Values (1);

Select * From Gtmp_Commit_Scn;

-------

--4943666

Insert Into Y1

Select Level, Null From Dual Connect By Level < 10;;

Select * From Gtmp_Commit_Scn;

-------

--4943666

Commit;

Select * From Gtmp_Commit_Scn;

-------

--4943671

update t1 set commit_Scn = (Select g.commit_Scn From Gtmp_Commit_Scn g) /*where ...*/;

update t2 set commit_Scn = (Select g.commit_Scn From Gtmp_Commit_Scn g) /*where ...*/;

truncate table Gtmp_Commit_Scn ;

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.

See you.