I would like to know why I am getting different SCN number for the below quires
SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL - I USE this for POINT IN TIME RECOVER FOR TABLESPACE .
SELECT CURRENT_SCN FROM V$DATABASE. - I use this for Database Recovery(RMAN)
WHY I AM GETTING TWO DIFFERENT SCN ?
I know the basic of SCN , but still I am confused . Can anyone clarify what is the exact meaning of the query
timestamp_to_scn gives an approximate result. In any given second, a database is likely to go through thousands of SCNs so the result cannot be exact. And it would be terribly expensive to maintain a table that associated a timestamp with every SCN that the system had ever encountered. Under the covers, Oracle maintains a table that stores the current SCN every few seconds and keeps that data for a few days. In recent versions, the granularity of that table is 1 SCN every 3 seconds though that may change over time.
When you call timestamp_to_scn, therefore, you get an SCN that was created within a few seconds of the date you're interested in but it's never going to be exact and it's not going to work forever. That's generally close enough for a point in time recovery-- you know that you want to restore to May 20, 2015 at 12:05:00 am but you don't really care if you restore to a state a second or two earlier or later. If you're identifying a particular bad transaction that you want to restore the system to (or to just before), you wouldn't want to use timestamp_to_scn.