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 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

 

 

 


 

 

 

 

 

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

 
Follow

Get every new post delivered to your Inbox.

Join 217 other followers

%d bloggers like this: