In many cases we need to know SCN of database. Also we may need to know the previous SCN of the database suppose before 10 minutes ago. I will try to write we can can get these two and how is this correlated.
A)Determine current SCN of The database.
-----------------------------------------------------------------------
You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.
1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
1053658
2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1053668
B)Determine current timestamp value.
-------------------------------------------------------------------
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
14-MAY-08 11.00.08.374107 PM -04:00
C)Convert SCN to Timestamp.
-----------------------------------------------------
SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;
SCN_TO_TIMESTAMP(1053639)
---------------------------------------------------------------------------
14-MAY-08 10.52.15.000000000 PM
D)Convert Timestamp to SCN
-------------------------------------------------------------
SQL> SELECT TIMESTAMP_TO_SCN('14-MAY-08 11.00.08.374107 PM') FROM DUAL;
TIMESTAMP_TO_SCN('14-MAY-0811.00.08.374107PM')
----------------------------------------------
1054516
thanks
ReplyDelete