Oracle DBA – A lifelong learning experience

Converting an SCN to a timestamp

Posted by John Hallas on June 27, 2014

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

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;






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


select SCN_TO_TIMESTAMP(10900098356623) from dual;




27-JUN-14 AM



To get a SCN from a timestamp


   WHERE order_id = 5000;




To get the current SCN in 9i only

select max(scn_bas) SCN_BASE from smon_scn_time;






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









Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 275 other followers

%d bloggers like this: