14 Replies Latest reply: Jan 23, 2013 12:32 PM by rp0428 RSS

    is scn is uniquly generated every time .

    947771
      Hi,
      i am newbie,

      i noticed that same scn is generated at commit for more than one row in a transaction.

      my worry is, can a same scn come again, though in doc's it 's written (SCNs occur in a monotonically increasing sequence).
      but still i want to know.

      for example

      two rows with (primary key) empid 1 and 2 in a table emp has same scn no 2407210.

      after some time or years if i update name column of above rows empid 1 and 2 in table emp ,
      then
      Q1) is it possible for database to generate same scn (2407210) again. for rows having empid 1 and 2?
      Q2) is it possible for database to generate same scn (2407210) of rows empid 1 and 2 again in any other situation for any other event.
      yours sincerely

      Edited by: 944768 on Jan 22, 2013 1:19 AM

      Edited by: 944768 on Jan 22, 2013 1:24 AM
        • 1. Re: is scn is uniquly generated every time .
          BluShadow
          944768 wrote:
          Hi,
          i am newbie,

          i noticed that same scn is generated at commit for more than one row in a transaction.

          my worry is, can a same scn come again, though in doc's it 's written (SCNs occur in a monotonically increasing sequence).
          but still i want to know.

          for example

          two rows with (primary key) empid 1 and 2 in a table emp has same scn no 2407210.

          after some time or years if i update name column of above rows empid 1 and 2 in table emp ,
          then is it possible for database to generate same scn (2407210) again. for rows having empid 1 and 2.

          yours sincerely
          Theoretically it's possible that the same SCN may occur again depending on hardware setup etc. though it's very very unlikely.

          >
          System Change Number. A database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.
          >

          However, why worry about it? You shouldn't be storing the SCN for your own use as some unique identifier anyway. Why do you need to know?
          • 2. Re: is scn is uniquly generated every time .
            Hemant K Chitale
            If you rebuild the database through
            Export
            Drop Database (if rebuilding in-situ)
            Create new Database (in-situ or another database or on another server)
            Import

            You could find SCNs reappearing because the CREATE DATABASE would have started SCNs afresh.


            Hemant K Chitale
            • 3. Re: is scn is uniquly generated every time .
              947771
              i want to use it for optimistic concurrency.

              that is I want to read the ORA_ROWSCN for any row lets say empid 1 from emp table in a web site page,then while updating i am readiing the ora_rowscn again for empid1 from emp table then comparing both old and new value of ora_rowscn to find if any body has changed it or not,so that we can take decision to show message to load the page or upadte.

              yours sincerely
              • 4. Re: is scn is uniquly generated every time .
                947771
                the situations u have stated , that is after new scn is started , if any one

                write select ORA_ROWSCN from emp where empid=1

                then what he will get 2407210 or (1 if scn starts with 1) ?

                yours sincerely

                Edited by: 944768 on Jan 22, 2013 3:39 AM

                Edited by: 944768 on Jan 22, 2013 3:42 AM
                • 5. Re: is scn is uniquly generated every time .
                  padders
                  that is I want to read the ORA_ROWSCN for any row
                  I believe SCN is tracked at block level unless you specify ROWDEPENDENCIES when creating the table.
                  • 6. Re: is scn is uniquly generated every time .
                    Hoek
                    944768 wrote:
                    i want to use it for optimistic concurrency.
                    Read:
                    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2680538100346782134
                    • 7. Re: is scn is uniquly generated every time .
                      947771
                      i have specified while creating table.

                      yours sincererly
                      • 8. Re: is scn is uniquly generated every time .
                        padders
                        You will want to read Hoek's link, it describes a serious issue with this approach.
                        • 9. Re: is scn is uniquly generated every time .
                          947771
                          we have read the link way back , last year.
                          my question was some thing else initialy if u consider my question objectivily.

                          yours sincerely.

                          Edited by: 944768 on Jan 23, 2013 12:28 AM

                          Edited by: 944768 on Jan 23, 2013 12:29 AM
                          • 10. Re: is scn is uniquly generated every time .
                            BluShadow
                            944768 wrote:
                            we have read the link way back , last year.
                            my question was some thing else initialy if u consider my question objectivily.
                            Your question is very brief and somewhat ambiguous.

                            Perhaps if you could clarify with exact details, people could help more, but until then, people are just going to be guessing what you mean.
                            • 11. Re: is scn is uniquly generated every time .
                              Billy~Verreynne
                              As I understand the OP, he wants to use SCN for optimistic locking.

                              T1. Front-end/app layer reads row (no pessimistic locking supported by stateless client-server) with SCN for row from database.

                              T2. Front-end/app layer updates row and send update to database.

                              T3. Database issues the update using the supplied SCN and matching that to the row's existing SCN. If different, then another session changed the row, and overwriting that change will result in lost update. Without app layer's row SCN matching the existing SCN, no update can happen.

                              Optimistic locks in this sense can last as long as the app layer session are configured to be idle. On OTN for example , I think that is 8 hours.
                              • 12. Re: is scn is uniquly generated every time .
                                Stew Ashton
                                Your original question was
                                944768 wrote:
                                my worry is, can a same scn come again, though in doc's it 's written (SCNs occur in a monotonically increasing sequence).
                                The answer is no. Nothing will work if the SCN "wraps around".

                                Later you said you wanted to use the SCN for optimistic locking. In the link you say you read, Tom Kyte explains why this is not a recommended approach.

                                So the answer to the original question doesn't mean you can use the SCN for optimistic locking.
                                • 13. Re: is scn is uniquly generated every time .
                                  user62721
                                  Hi,

                                  SCN is 48-bit
                                  1000 TX/s => more than 8000 years

                                  Regards
                                  • 14. Re: is scn is uniquly generated every time .
                                    rp0428
                                    >
                                    As I understand the OP, he wants to use SCN for optimistic locking.
                                    >
                                    Please explain your response to BluShadow. He was responding to OPs comment about having read Hoek's AskTom link and saying that it didn't apply to his use case.

                                    But that article IS about 'ORA_ROWSCN for optimistic locking'.

                                    So I agree with BluShadow that if 'optimistic locking' isn't the problem being asked about then OP needs t clarify the problem since the 'wrap' issue for SCNs is irrevelant as far as the validity of using ORA_ROWSCN for optimistic locking is concerned.