Query Current SCN ?

[mahoori]

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.

[pando]

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER (
RETURN NUMBER);

[mahoori]

Thanks pando.
I forget desc DBMS_FLASHBACK .
BEST REGARDS.

[chao_ping]

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+?

[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

[julian]

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:

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?

[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.

[julian]

10x Jurij! In between, I found out that

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.

[sreddy]

Thanks guys for interesting insight info of SCN.