-
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.
-
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER (
RETURN NUMBER);
-
Thanks pando.
I forget desc DBMS_FLASHBACK .
BEST REGARDS.
-
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
-
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?
-
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
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|