When inserting a row I want to make a column, equal to the current time on commit.

If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly different to commit time). If I use an on insert trigger, it again uses the time at the insert - not the actual commit.

Are there any possible solutions to get current time on commit with oracle ?

  • What would happen if you query the table after doing the insert but before doing a commit? (from the same session)
    – ruudvan
    May 19, 2016 at 15:49
  • The only way of doing this is using an ON COMMIT REFRESH materialised view, with triggers on it that update the original table with the current timestamp. You'd need a way of stopping it getting in an infinite loop though
    – Philᵀᴹ
    May 19, 2016 at 16:01
  • Why do you even want this, by the way? Seems a bit of a nonsensical requirement. What's stopping you from changing your codebase to update/log the commit time after the commit? What is actually performing the commit?
    – Philᵀᴹ
    May 19, 2016 at 18:49
  • @Phil "What's stopping you from changing your codebase to update/log the commit time after the commit?" how ? There is no ON COMMIT trigger mechanism in Oracle, afaik. May 20, 2016 at 7:35
  • 3
    I would be very interested to know what problem this is intended to solve or what business requirement it intends to satisfy. On the face of it it seems like a strange request. May 20, 2016 at 8:37

3 Answers 3


To get an approximate value (+/- 3sec or so) you may use ORA_ROWSCN

You must define the table with ROWDEPENDENCIES to have the SCN stored for each row.

Here an simple example:

create table t1 
(c1 number,
c2 date)

insert into t1 values(1,sysdate);
-- wait 5 sec

insert into t1 values(2,sysdate);
-- wait 10 sec


ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)         C1 C2                
---------- ---------------------------- ---------- -------------------
   1.4E+13 19.05.16 19:14:15,000000000           1 19.05.2016 19:14:09 
   1.4E+13 19.05.16 19:14:30,000000000           2 19.05.2016 19:14:21 
  • 2
    This is not a reliable way to get commit times. The only thing guaranteed is that the time that the commit happened is less than the SCN time. See this and explained in the docs here. Also, you can't go too far back in time since the SCN mappings are lost after the redo/flashback window, which depending on the environment could just be a couple of days.
    – ruudvan
    May 19, 2016 at 17:43


  1. collect ROWID for each line updated
  2. get the systimestamp just before the "real" commit
  3. update the record by collected rowid with the previous timestamp
  4. commit one again

But as stated in comments

What is the business requirement for this?

It has a big defect, it's commiting twice for the same records, so generating almost double redolog for each run.


As other have asked, it would be helpful to know what business requirements need this functionality.

For example, if you need to know when a certain row started appearing in query results for other sessions (which would only occur on the commit, not the insert), then you could rename your table and add a column that is an effective date. When you insert into the table you could set the effective date to something in the future after you know the commit will have occurred. You could then create a view named the same as the original table and have it show only rows with effective dates less than sysdate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.