DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Query Current SCN ?

  1. #1
    Join Date
    Jun 2001
    Posts
    103
    Hi all.
    I need current SCN of database .
    which V$ view contains last SCN generated in database?
    I need last SCN for testing flashback query in 9iDB.
    Best Regards.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER (
    RETURN NUMBER);


  3. #3
    Join Date
    Jun 2001
    Posts
    103
    Thanks pando.
    I forget desc DBMS_FLASHBACK .
    BEST REGARDS.

  4. #4
    Originally posted by mahoori
    Thanks pando.
    I forget desc DBMS_FLASHBACK .
    BEST REGARDS.
    Is there any way to get the same answer in oracle 8/8i+?
    www.cnoug.org

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by chao_ping
    Is there any way to get the same answer in oracle 8/8i+?
    http://www.dbasupport.com/forums/sho...threadid=21223
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Originally posted by chao_ping
    Is there any way to get the same answer in oracle 8/8i+?
    http://www.dbasupport.com/forums/sho...threadid=21223
    Jurij, how would you explain this in 9.2:

    Code:
    SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      954297
    
    SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      954298
    
    SQL> SELECT MAX(ktuxescnw * POWER(2, 32) + ktuxescnb) FROM x$ktuxe;
    
    MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)
    ------------------------------------
                                  954295
    
    SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      954302
    
    SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      954303
    Why isn't the sequence monotonically increasing?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    For a start, I should have noted that both mentioned methods (USERENV('COMMITSCN') and MAX(ktuxescnw * POWER(2, 32) + ktuxescnb) FROM x$ktuxe) return only approximate current SCN. In fact they provide you with so called commit SCN, which is generally speaking a SCN of the most recently commited transaction, and that can be slightly different than the SCN reported by DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER().

    Commit SCN usually lags behind current SCN reported by DBMS_FLASHBACK for a few numbers.

    One of the problems with getting the SCN is also the fact that SCN has quite a few different meanings (current SCN, commit SCN, transaction start SCN, checkpoint SCN, .....). Maybe you can get more information about this if you search ixora site for "x$ktuxe".

    The other thing is your question about "monotonically increasing" number. Well, theoretically it is, however it will sometimes appear as it is skipping some numbers. Sometimes it will increase the numbers so fast (or probably they are protected by latches/enqueues, I don't know) that you simply won't be able to see some of them and it will appear as they were skipped. For example, here is an output from my SQL/Plus session, where I was the only user connected on the system:
    Code:
    SQL> SELECT dbms_flashback.get_system_change_number FROM dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275758
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275759
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275759
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275760
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275766
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275784
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275784
    
    SQL> /
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                      275784
    See, it jumped from 275760 to 275766 and then again to 275784. Note that I'm the only user on the system and I repeated the command in SQL*Plus pretty fast, approx once per second.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    10x Jurij! In between, I found out that

    Code:
    SELECT MAX(ktuxescnw * POWER(2, 32) + ktuxescnb) FROM x$ktuxe;
    returns the SCN at which the last committed transaction did a commit, which is not exactly the same thing as current SCN. The reason why the above statement returned a little bit smaller number is that you should add in the ktuxescnw as well, but according to Jonathan Lewis this 'wrap' only occurs every billion or so commits, so it is probably ignored.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Thanks guys for interesting insight info of SCN.
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width