Block Sequence Number & SCN

In addition to SCNs, Block Sequence Numbers which are used to distinguish updates within a block and same SCN. When a block is updated, Oracle records the current SCN in the block. In addition, Oracle also records a sequence number for the change i.e. an SCN+Sequence number identifies a change within a block. The basic idea is to store the current redo generating SCN in the cache header when making a change to a block. Since multiple changes to a block can be made at the same SCN, a sequence number is also stored in the cache header to differentiate between different changes at the same SCN. The sequence number is increased each time a change is made at the same SCN. The sequence number is reset to 1 when making a change at a higher SCN than the SCN currently in the block. This allows multiple changes to the same block to have the same SCN (though they will have different sequence numbers). An SCN is stored in the Data Block, signifying the SCN at which the latest (most recent) change to the block took place.

 

Let’s explore the SCN and sequence at the block level. We have one table TEST with two columns Name varchar2(40) and CITY varchar2(20).

SQL> create table TEST (NAME Varchar2(20) , SAL number);
Table created.

SQL> insert into Test values ('AMIT',100);
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','TEST') FNO, DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLK, NAME,SAL FROM SYS.TEST ;

FNO               BLK                 NAME        SAL
---------- ---------- -------------------- ----------
1               86505                 AMIT        100


So we identified that table containing record where NAME=’AMIT’ in block number 86505 of file number (absolute) 1. Now we will try to perform some transactions followed by block dump to see the change in SCN and sequence number pair at block level.