How to map SCN with Timestamp before 10g [ID 365536.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.6.0
Information in this document applies to any platform.

Goal

In this note, we will help Database Administrators with answers to two common questions:
How can I know the time of last transaction (time of last SCN)?
How can I map SCN with Timestamp prior to 10g?

Solution

SCN <-> TIMESTAMP conversion features are available only from Oracle 10g. In 10g, there are two built-in functions to give the timestamp and SCN mapping details--Timestamp_to_scn() and Scn_to_timestamp().

For example: In 10g, you will issue code like,

SQL> select current_scn from v$database;
CURRENT_SCN
---------------------------
1086382

SQL> Select scn_to_timestamp(1086382) from dual;

SCN_TO_TIMESTAMP(1086382)
---------------------------------------------------------------------------
15-DEC-05 09.39.49.000000000 PM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

However, in earlier releases, while there is a system object - SYS.SMON_SCN_TIME that will provide the SCN to TIME mapping information. There is no conversion facility provided.

SYS.SMON_SCN_TIME will have a maximum of 1440 rows and each record will be for a 5 minute period. Oracle maintains this information for maximum of 5 days after which the records will be recycled.

This means that data is stored 12 times per hour * 24 hours * 5 days=1440 rows.

SCN value is stored internally as :
i. SCN_wrap
ii. SCN_base

Whenever the SCN is incremented, the BASE component is incremented first unil it reaches it maximum. Once the BASE reaches the maximum value allowed, it is initialized to zero again after incrementing the WRAP by 1.

Using this logic, we can calculate the timestamp of the SCN as follows:

(SCN_WRP * 4294967296) + SCN_BAS should give us the SCN in the number format

To get the time/date for an SCN value in 9i, use the following example:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- (a) Get the current SCN base.
SQL>select max(scn_bas) SCN_BASE from smon_scn_time;

1603342197

--(b) Get the complete SCN and the timestamp.

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

SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time where
scn_bas='1603342197';

TIMESTAMP SCN
------------------ ----------
28-JUL-06 05:31:08 8252235517813