小宝老豆的专栏

小宝老豆出品必出精品

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

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


 

Modified 16-MAY-2006     Type HOWTO     Status PUBLISHED

 

In this Document
  Goal
  Solution


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


 

 

 

 

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

阅读更多
个人分类: Oracle 高级知识
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭