Converting an SCN to a timestamp

Something nice and simple

It is easy to get the current SCN from the database and to map between SCN and timestamp because from 10g onwards there are 2 functions to convert between SCN and TIMESTAMP

In 9i there was also a means of mapping an SCN to a TIMESTAMP. This involves the system view SYS.SMON_SCN_TIME. The view no longer exists in 12c. One limitation is that it only holds 1440 rows (well it does at 9i but I can see 2711 rows in an 11GR1 database and 2500 in an 11GR2 one) and therefore cannot hold more than 5 days worth of history. There is a good blog entry explaining this at http://orainternals.wordpress.com/2012/01/19/scn-what-why-and-how/

I will demonstrate the methods below

 

col current_scn form 999999999999999999

col TIMESTAMP_TO_SCN form 9999999999999999

col NOW10G form 99999999999999999999

col NOW9I form 99999999999999999999

col SCN form 99999999999999999999

col TIMESTAMP form 99999999999999999999

alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';

 

Normal 10G onward syntax

 

 select current_scn from v$database;

 

       CURRENT_SCN

-------------------

     10900098356623

 

To convert an SCN to a TIMESTAMP the SCN_TO_TIMESTAMP (10g onwards) is used

 

select SCN_TO_TIMESTAMP(10900098356623) from dual;

 

SCN_TO_TIMESTAMP(10900098356623)

---------------------------------------------------------------------------

27-JUN-14 07.31.31.000000000 AM

 

 

To get a SCN from a timestamp

 

SELECT TIMESTAMP_TO_SCN(order_date) FROM orders
   WHERE order_id = 5000;

TIMESTAMP_TO_SCN(ORDER_DATE)
----------------------------
                      574107

 

 

To get the current SCN in 9i only

select max(scn_bas) SCN_BASE from smon_scn_time;

 

 SCN_BASE

----------

3766338378

 

select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from sys.smon_scn_time where scn_bas = '3766338378'

TIMESTAMP                           SCN

------------------ ---------------------

27-JUN-14 06:44:26       10900098368330