SCN on Scale of Time & SCN of a ROW

In the last post we have discussed about SCN structure and generation schemes, now we will see relation of SCN with timestamp and association with data populated in database tables.

SCN to TIMESTAMP: SCN is actually generated based on timestamp as well. So it’s easy to check timestamp from SCN and vice versa. Handy conversion features are available only from Oracle 10g onwards, but still from older versions Oracle had system object SYS.SMON_SCN_TIME, which can provide you the same information after some simple calculations.

In 10g onwards, there are two built-in functions to give the timestamp and SCN mapping details timestamp_to_scn() & scn_to_timestamp()

SQL> select scn_to_timestamp(current_scn-rownum) tm,
timestamp_to_scn(scn_to_timestamp(current_scn-rownum)) scn, current_scn-rownum
from v$database
connect by level <=5;

TM                                        SCN         CURRENT_SCN-ROWNUM
----------------------------------- ----------------- ------------------
14-AUG-12 AM               1274785            1274785
14-AUG-12 AM               1274784            1274784
14-AUG-12 AM               1274782            1274783
14-AUG-12 AM               1274782            1274782
14-AUG-12 AM               1274781            1274781

Oracle Documentation is saying that both functions actually trying to give approximate SCN or timestamp value. Here are the definitions:

TIMESTAMP_TO_SCN: takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of datatype NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp.

SCN_TO_TIMESTAMP: takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN. For example, it can be used in conjunction with the ORA_ROWSCN pseudo column to associate a timestamp with the most recent change to a row.

SYS.SMON_SCN_TIME: There is a system object SYS.SMON_SCN_TIME that will provide the SCN to TIME mapping information, there is no conversion facility provided. It will have a maximum of 1440 rows and each record will be for a 5 minute period. Oracle maintains this information for a 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.  This behavior differs in different releases as in I can see entries are populated every minutes or sometimes after 7-9 minutes as well. While is giving consistent behavior in approx timely manner.

(SCN_WRP * 2^32) + SCN_BAS should give us the SCN in the number format. Let’s get the current SCN base.

SQL> select max(scn_bas) SCN_BASE from sys.smon_scn_time;


Get the complete SCN and the timestamp:

SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> select time_dp TIMESTAMP, scn_wrp*power(2,32) +SCN_bas SCN from sys.smon_scn_time where scn_bas='1497976';

TIMESTAMP               SCN
------------------ ----------
07-MAY-14 09:30:04    1497976

In my case SCN_WRP is 0 because it’s a new database created for testing purpose.


ORA_SCN pseudo column reports the latest SCN of the block holding that row. Here in the demonstration we will see the SCN value for different rows in multiple blocks, where all rows from same block will report same timestamp.

We have created table TEST with fixed length CHAR(2000) column so we can make sure that we will not be able to hold more than 3 rows in same block of 8K size.

SQL> desc test

Name                 Null?      Type
----------------- -------- ------------
NAME                         CHAR(2000)
SAL                              NUMBER


BLK             SCN    NAME                      SAL
---------- ---------- -------------------- ----------
86505         1560754 AMIT                        100
86505         1560754 NEERAJ                      150
86505         1560754 ROHIT                       300
86506         1560964 DEEPAK                      352

We can see first three rows belong to block 86505 while last row belongs to block 86506. Let’s update one row from first block to see the SCN reflection for other rows.

SQL> update test set sal=sal+10 where name='NEERAJ';

1 row updated.

SQL> commit;

Commit complete.


BLK             SCN    NAME                      SAL
---------- ---------- -------------------- ----------
86505         1561475 AMIT                        100
86505         1561475 NEERAJ                      160
86505         1561475 ROHIT                       300
86506         1560964 DEEPAK                      352

Here we have observed that all rows from block 86505 showing new SCN 1561475 while row from block 86506 is showing SCN 1560964. Its because Oracle maintains SCN in block header and any change in block results in change in SCN as well. This is basic design principle from recovery perspective.

Mapping of SCN and Time, We could get multiple SCN generated in almost same time (within portion of 3 seconds)?

Timestamp_to_scn and SCN_to_timestamp functions would give the answers with precision of 3 seconds so it could happen that you may get same timestamp for multiple SCNs yet each and every SCN is distinct. Vice a versa is also true.

SQL> select scn_to_timestamp(current_scn-rownum) time, current_scn-rownum SCN from v$database connect by level<=20 order by SCN;

TIME                                           SCN

---------------------------------------- ----------
09-MAY-14 PM             1561595
09-MAY-14 PM             1561596
09-MAY-14 PM             1561597
09-MAY-14 PM             1561598
09-MAY-14 PM             1561599
09-MAY-14 PM             1561600
09-MAY-14 PM             1561601
09-MAY-14 PM             1561602
09-MAY-14 PM             1561603
09-MAY-14 PM             1561604
09-MAY-14 PM             1561605
09-MAY-14 PM             1561606
09-MAY-14 PM             1561607
09-MAY-14 PM             1561608
09-MAY-14 PM             1561609
09-MAY-14 PM             1561610
09-MAY-14 PM             1561611
09-MAY-14 PM             1561612
09-MAY-14 PM             1561613
09-MAY-14 PM             1561614

20 rows selected.

From one busy system the SCN generation rate was showing approx 1500 SCN generation every 3 seconds. So in other way around this information is approximate true, as mapping precision is 3 seconds. Why 3 sec, well lot many things designed around this fundamental even of getting triggered every 3 seconds and this could also be mapped same way to avoid performance overhead by reducing to 1 or 2 second.

SQL> select scn_to_timestamp(current_scn-rownum) time , count(*) from v$database connect by level<=10000 group by scn_to_timestamp(current_scn-rownum) order by 1;

TIME                                     COUNT(*)
---------------------------------- --------------
10-May-14 AM               538
10-May-14 AM              1335
10-May-14 AM              1727
10-May-14 AM              1893
10-May-14 AM              1657
10-May-14 AM              2751
10-May-14 AM                99

7 rows selected.

Maintenance of SMON_SCN_TIME Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table. This should allow you to check the content of the table (count(*) for number of rows etc, analyze validate to confirm if it is corrupt or not, plus check the actual row content in case there are any timestamps in the table in the future). The content of this table just maintains a rough mapping between timestamps and SCN values so if there is excess rows or rows in the future, then you can delete rows from the table manually to get back to a sensible start point. The SMON time mapping is mainly for flashback type queries to map a time to an SCN. So it is probably simplest to copy the content to a holding table then delete ALL rows, then recycle the instance. SMON should start to populate the table with new time / SCN pairs from the time that the instance is started.

SQL> conn / as sysdba

/* Set the event at system level */

SQL> alter system set events ‘12500 trace name context forever, level 10’;

/* Delete the records from SMON_SCN_TIME */

SQL> delete from smon_scn_time;

SQL> commit;
SQL> alter system set events ‘12500 trace name context off’;

The AWR report shows the following SQL with an excessive amount of executions:

delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0);

This delete statement deletes the oldest rows from smon_scn_time to clear space for new rows.  SMON wakes up every 5 minutes and checks how many on-disk mappings we have against the max limit. The new mappings are then added for the last period (since SMON last updated), and if this is over max mapping, SMON will then issue the delete statement. There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does not free enough mappings, then there will be multiple executions. What happens is due to the inconsistency between the table and indexes the delete returns zero rows; so the delete statement is executed continuously to reduce the smon_scn_time below the maximum mappings.

Here are links to our other posts in SCN series

System Change Number (SCN)
Checkpoint & SCN
Role of SCN in Instance Recovery & Media Recovery
SCN Structure & Generation Schemes
Read Consistency & SCN
Block Sequence Number & SCN

The views expressed on this blog are my own and do not necessarily reflect the views of either the companies I have worked for or Oracle Corporation and its affiliates. The comments, views and opinions expressed by visitors on this blog are theirs alone and may not reflect mine. Whatever scenarios suggested under this blog were simulated only on demo environment, so it’s advisable to test those in test systems before pushing to your production environment.


6 Responses to SCN on Scale of Time & SCN of a ROW

  1. Pingback: System Change Number (SCN) | Amit Saraswat

  2. Pingback: Checkpoint & SCN | Amit Saraswat

  3. Pingback: Role of SCN in Instance Recovery & Media Recovery | Amit Saraswat

  4. Pingback: SCN Structure & Generation Schemes | Amit Saraswat

  5. Pingback: Read Consistency & SCN | Amit Saraswat

  6. Pingback: Block Sequence Number & SCN | Amit Saraswat

Share Your Comment

Please log in using one of these methods to post your comment: 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

Pierre blog

Pierre Forstmann Oracle Database blog


Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: