This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Oct 21, 2013 3:52 AM by yxes2013 RSS

scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)

yxes2013 Newbie
Currently Being Moderated

Hi all,


11.2.0.1


I read in the docs that there is a function that tagged each row in a table a unique row identifier called scn_to timestamp.

This is similar to rowid but its sequential. I tried the following command:


select scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) from   EMP  where rownum<101;


SCN_TO_TIMESTAMP(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)

---------------------------------------------------------------------------

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

21-OCT-13 10.26.46.000000000 AM

 

100 rows selected.

 

 

Why are they all duplicate in timestamp? How can I select the next batch excluding the first batch I selected?

 

Please help....

 

Thanks a lot,

zxy

  • 1. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Alvaro Pro
    Currently Being Moderated

    Can you please shared the documentation?

     

    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER gets the CURRENT SCN of your database: DBMS_FLASHBACK


    And then you're using scn_to_timestamp to simply convert your current SCN to a timestamp. That's why they are all of the same timestamp,cuz you're simply converting your current scn to timestamp.


    I think what you mean to do is get the row scn from the ORA_ROWSCN pseudo-column.


    The correct SQL would be something like:


    SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP WHERE rownum < 101;

     

    Read: SCN_TO_TIMESTAMP

  • 2. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    I'm not sure what documentation you're reading but you either need to find better documentation or you need to slow down and read it more carefully.

     

    There is a pseudo-column ORA_ROWSCN.  By default, this is stored at the **block** level, not at the row level.  It is stored at the row level if and only if the table was originally built with ROWDEPENDENCIES enabled.

     

    If you take the ORA_ROWSCN and pass it to the SCN_TO_TIMESTAMP function, you will get back the approximate timestamp (for 11.2, I believe that is +/- 3 seconds or so) assuming that Oracle still has the SCN to timestamp mapping.  That should generally let you go back a few days-- on the order of a week in most databases.

     

    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER returns the system's current SCN.  It makes no sense to use this in a query against the EMP table since it will, by definition, return the same value for each row.


    Justin

  • 3. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    TSharma-Oracle Guru
    Currently Being Moderated

    GET_SYSTEM_CHANGE_NUMBER Function

    This function returns the current SCN as an Oracle number datatype. You can obtain the current change number and store it for later use. This helps you retain specific snapshots.

     

    So you are just converting the same current SCN to timestamp. That is why you are seeing the same values.

  • 4. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Al, Justin, Tsh

     

    You are really all bright boys

     

    But there is still duplicates how do I select the next batch of 100 without including the first batch? Thanks a lot.

     

      1* SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP where rownum<101

     

    21-OCT-13 07.21.45.000000000 AM

    21-OCT-13 07.21.45.000000000 AM

    21-OCT-13 07.21.45.000000000 AM

    21-OCT-13 07.21.45.000000000 AM

    21-OCT-13 07.21.45.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.23.00.000000000 AM

    21-OCT-13 07.24.27.000000000 AM

    21-OCT-13 07.24.27.000000000 AM

    21-OCT-13 07.24.27.000000000 AM

    21-OCT-13 07.24.27.000000000 AM

    21-OCT-13 07.24.27.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.25.48.000000000 AM

    21-OCT-13 07.02.50.000000000 AM

    21-OCT-13 07.02.50.000000000 AM

    21-OCT-13 07.02.50.000000000 AM

    21-OCT-13 07.02.50.000000000 AM

    21-OCT-13 07.02.50.000000000 AM

    21-OCT-13 07.04.47.000000000 AM

    21-OCT-13 07.04.47.000000000 AM

    21-OCT-13 07.04.47.000000000 AM

    21-OCT-13 07.04.47.000000000 AM

    21-OCT-13 07.04.47.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.06.26.000000000 AM

    21-OCT-13 07.08.03.000000000 AM

    21-OCT-13 07.08.03.000000000 AM

    21-OCT-13 07.08.03.000000000 AM

    21-OCT-13 07.08.03.000000000 AM

    21-OCT-13 07.08.03.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.09.30.000000000 AM

    21-OCT-13 07.11.06.000000000 AM

    21-OCT-13 07.11.06.000000000 AM

    21-OCT-13 07.11.06.000000000 AM

    21-OCT-13 07.11.06.000000000 AM

    21-OCT-13 07.11.06.000000000 AM

    21-OCT-13 07.12.30.000000000 AM

    21-OCT-13 07.12.30.000000000 AM

    21-OCT-13 07.12.30.000000000 AM

    21-OCT-13 07.12.30.000000000 AM

    21-OCT-13 07.12.30.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.14.12.000000000 AM

    21-OCT-13 07.15.45.000000000 AM

    21-OCT-13 07.15.45.000000000 AM

    21-OCT-13 07.15.45.000000000 AM

    21-OCT-13 07.15.45.000000000 AM

    21-OCT-13 07.15.45.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.17.12.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.18.33.000000000 AM

    21-OCT-13 07.20.06.000000000 AM

    21-OCT-13 07.20.06.000000000 AM

    21-OCT-13 07.20.06.000000000 AM

    21-OCT-13 07.20.06.000000000 AM

    21-OCT-13 07.20.06.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.21.48.000000000 AM

    21-OCT-13 07.23.06.000000000 AM

    21-OCT-13 07.23.06.000000000 AM

    21-OCT-13 07.23.06.000000000 AM

    21-OCT-13 07.23.06.000000000 AM

    21-OCT-13 07.23.06.000000000 AM

    21-OCT-13 07.24.33.000000000 AM

    21-OCT-13 07.24.33.000000000 AM

     

     

    100 rows selected.

     

     

    SQL>

  • 5. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    What do you mean by "next batch"?  And what does a "batch" have to do with the SCN?

     

    Justin

  • 6. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    TSharma-Oracle Guru
    Currently Being Moderated

    For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row


    you will need to do something like


    SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP whereemp_id = <id number>?


    Read the docs.

  • 7. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Alvaro Pro
    Currently Being Moderated

    As JustinCave has noted, this is likely due to the fact that this table was created without ROWDEPENDENCIES clause (default).

     

    Which means the rows that have the "duplicates" probably reside on the same block and that's why you see the same ORA_ROWSCN (it is maintained at the block level).

  • 8. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Hi Justin,

     

    I am processing by batch:

    EMP is just an example. I have a table that is so volatile lots of inserts every seconds. Average rows of 12 records per second.

    I am selecting summary total SUM(AMOUNT) for all rows selected from 1AM to 2AM. Then selecting next batch of SUM(AMOUNT) again from (2AM-3AM) excluding the previous SUM or batch, and so on up to 11PM-12AM.

     

    There is a unique column TRANSID but it is not indexed. But I think I can negotiate it to be indexed if needed.


    How how can I query the next batch? would it be


    SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP where SCN_TO_TIMESTAMP(ORA_ROWSCN) > than the previous saved one?


    Thanks,

  • 9. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    Is your table built with ROWDEPENDENCIES?

     

    Are rows ever modified?  Or are they always static once they are inserted?

     

    Are you only counting things that happened today?  Or do you need to go back further in time (in which case the length of time that SCN_TO_TIMESTAMP retains its mappings may be problematic?

     

    Is the approximate nature of the SCN to timestamp mapping acceptable?  Does it matter if rows that were actually modified a few seconds before 11am get counted in the 11am - noon set?

     

    Do you care about performance at all?  The ORA_SCN is not indexed so you'd be doing a full table scan for every single hour if you ran the query you proposed.  That seems unlikely to be reasonable.

     

    Taking a step back, though, if you need to group transactions by the time they occurred, it makes radically more sense to store the transaction time (either a DATE or a TIMESTAMP depending on your precision requirements) and to index that in order to produce the results you want.  Hacking something together with SCNs is going to be much less efficient, much more error-prone, much more code, and much harder to follow.

     

    Justin

  • 10. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Tsh,

     

    SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP whereemp_id = <id number>?


    Is it possible to use rowid for uniqueness?


    SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM EMP where rowid not in previous_upper_bound rowid?


    Thanks,

  • 11. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    A ROWID is a physical address.  Barring exceptionally trivial cases, you cannot assume that a row will have the same ROWID over time or that rows that are inserted later will have greater ROWID values than rows that are inserted earlier.

     

    Justin

  • 12. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    yxes2013 Newbie
    Currently Being Moderated

    Thanks Justin,

     

    Is your table built with ROWDEPENDENCIES? This is a stand alone table.

     

    Are rows ever modified?  Or are they always static once they are inserted?  Mostly inserts and minimal updates or reversal correction.

     

    Are you only counting things that happened today?  Or do you need to go back further in time (in which case the length of time that SCN_TO_TIMESTAMP retains its mappings may be problematic?

    I am counting today only as this table is backed up every midnight and truncated.

     

    Is the approximate nature of the SCN to timestamp mapping acceptable?  Does it matter if rows that were actually modified a few seconds before 11am get counted in the 11am - noon set?

    Yes maybe a 1% degree of error is acceptable.

     

    Do you care about performance at all?  The ORA_SCN is not indexed so you'd be doing a full table scan for every single hour if you ran the query you proposed.  That seems unlikely to be reasonable.

    Performance is important hence we have to benchmark if acceptable. Is there a change to add indexes?

     

    Taking a step back, though, if you need to group transactions by the time they occurred, it makes radically more sense to store the transaction time (either a DATE or a TIMESTAMP depending on your precision requirements) and to index that in order to produce the results you want.  Hacking something together with SCNs is going to be much less efficient, much more error-prone, much more code, and much harder to follow.

    I agree, we are trying to negotiate about trigger ( as what Nic advised) to create another table withc unique sequence number. But for now we need a quick alternative

     

    Thanks.

  • 13. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    Justin Cave Oracle ACE
    Currently Being Moderated

    I have no idea what "a stand alone table" means.  But that doesn't answer the question.  When you created the table, did you specify ROWDEPENDENCIES?  Or did you leave the default (in which case the ORA_ROWSCN is stored at the block level).

     

    Are the updates minimal enough that you are comfortable having those minimal number of rows counted in the wrong time period and/or double-counted?

     

    You cannot index the ORA_ROWSCN.  If you're desperate to do something rather silly rather than just adding the column, you could do a single table scan and do a GROUP BY

     

    SELECT trunc( scn_to_timestamp( ora_rowscn ), 'HH24' ), count(*)
    FROM your_table
    GROUP BY trunc( scn_to_timestamp( ora_rowscn ), 'HH24' )

     

    Assuming that you did not build the table with ROWDEPENDENCIES enabled, my wager would be that the combination of not having the ORA_ROWSCN at the row level, the inherent imprecision of the SCN to timestamp mapping, and the presence of updates would make this way too inaccurate to actually use. 

     

    Justin

  • 14. Re: scn_to_timestamp(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER)
    TSharma-Oracle Guru
    Currently Being Moderated

    What are you actually trying to achieve? If you can explain more clearly , we may be bale to help. We are asking you to check  if you had created a table with ROWDEPENDENCIES or not. If not, then ora_rowscn will be maintained default at block level. So all the rows on a same block will have a same ora_rowscn number. So if you update 10 rows in 1 block , those all rows will have the same ora_rowscn number. This will give you a false positive result.

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points