tag:blogger.com,1999:blog-13265058.post112847993009008024..comments2023-10-28T23:33:56.980+11:00Comments on Sydney Oracle Lab: Recording the commit time on a recordSydOraclehttp://www.blogger.com/profile/08828771074492585943noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-13265058.post-1129495249253494322005-10-17T06:40:00.000+10:002005-10-17T06:40:00.000+10:00See another idea on this at http://www.niall.litch...See another idea on this at http://www.niall.litchfield.dial.pipex.com/2005/10/reinventing-wheel.html<BR/>.<BR/>BR,<BR/>MartinAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1128657855138887122005-10-07T14:04:00.000+10:002005-10-07T14:04:00.000+10:00I'll have a look at the DBMS_ALERT. I was planning...I'll have a look at the DBMS_ALERT. I was planning a followup demonstrating, rather than having the DBMS_JOB continually running in the background, it would be submitted by an AFTER INSER/UPDATE trigger, which would be more suitable for less active tables. I'll have to look at how DBMS_ALERT fits in with both.<BR/><BR/>On the SCN, while it does stand for System Commit Number (or System Change Number) and is incremented upon commit, I think the SCN value held against the INSERTed record is the one that was current for the database when the INSERT is done[1], not the one that is 'awarded' when the transaction commits. When the inserted record is written, Oracle doesn't know when the commit will happen and can't know the SCN that that commit will be awarded. And when the commit happens it doesn't go back an rewrite all those blocks with the updated SCN.<BR/>See Zhur's comment<BR/>http://asktom.oracle.com/pls/ask/f?p=4950:8:5511629952260217445::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:22948373947565<BR/><BR/>[1] I'm not sure exactly 'when' this 'when' is. <BR/>In this case, with rowdependencies and a direct path insert, it appears to be the time the INSERT statment started.<BR/>http://asktom.oracle.com/pls/ask/f?p=4950:8:5511629952260217445::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:44798632736844<BR/><BR/>But back on <BR/>http://asktom.oracle.com/pls/ask/f?p=4950:8:5511629952260217445::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:22948373947565<BR/>Andrew Mannering indicates the SCN isn't available directly after the update process. Maybe when it is written to the logfile ?<BR/><BR/>Material for a future blog...SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1128580115662329942005-10-06T16:28:00.000+10:002005-10-06T16:28:00.000+10:00Hello,.your approach is a good idea..In 9i, using ...Hello,<BR/>.<BR/>your approach is a good idea.<BR/>.<BR/>In 9i, using dbms_alert might allow for still more accurate commit_timestamps.<BR/>.<BR/>However in 10g the <BR/>* ORA_ROWSCN-function<BR/>seems to provide similar functionality out-of-the-box.<BR/>Checkout <BR/>* SCN_TO_TIMESTAMP(ORA_ROWSCN) <BR/>* CREATE TABLE .. ROWDEPENDENCIES<BR/>as well.<BR/>.<BR/>Thank you for sharing your solution,<BR/>MartinAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-13265058.post-1128542022037498402005-10-06T05:53:00.000+10:002005-10-06T05:53:00.000+10:00Very useful idea!Very useful idea!Anonymousnoreply@blogger.com