Query Current SCN ?

[jmodic]

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:

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.