0

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 '16 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 '16 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 '16 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. – NimChimpsky May 20 '16 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. – BriteSponge May 20 '16 at 8:37
1

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)
ROWDEPENDENCIES;

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

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

SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN), c1,c2 FROM t1;


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 
  • 1
    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 '16 at 17:43
0

In PL/SQL I'd

  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.

0

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, privacy policy and cookie policy

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