sequence / thread / system change number

I have seen a confusion between sequence and system change number quite often.

The sequence is a fairly little number which is the number of log switch since database creation (it can be resetted by open resetlogs).

The thread is only relevant in RAC. In single-instance database, it is always 1.

You can find the current sequence number with the following query


select sequence#,thread# 
from v$log 
where status='CURRENT';

The system change number (SCN) is a much higher number, which is continously increasing, even when you do nothing. The dbms_flashback package has a function to return the current system change number :


select dbms_flashback.get_system_change_number
from dual;

In 10g, there is a standard function to get the current system change number


select timestamp_to_scn(current_timestamp) from dual;

Before a major application/database upgrade, it is good practice to make a backup and write down the SCN for an easier recovery procedure.

read my comment about non-accuracy of timestamp_to_scn

7 thoughts on “sequence / thread / system change number”

  1. Hello,

    You said that SCN is continously increasing, what happens when it gets really big? Does this number overflow and starts from 0?

    Thank you.

  2. Yes, but Oracle is storing this value everywhere, in datafile, in control file, passes from one instance to another, and it is not a Oracle “number” datatype, it is only 4 bytes (at least on 32bit platforms), so I think there should be some kind of solutions, that Oracle is using when SCN gets big :)

  3. > 4 bytes

    you mean SCN cannot exceed 2^32 (4 billions) ? This is really small, a busy system can reach this in one day… it is hard to believe to have a non-unique scn, how would RMAN figure out what RESTORE UNTIL SCN means, if scn is not unique?

    Where did you read it is 4bytes?

    Well, the big systems mostly run 64bits today, but still I am worried about your comment :evil:

  4. a big note about timestamp_to_scn… it is not at all accurate

    
    SQL> select current_timestamp,
      2    timestamp_to_scn(current_timestamp) timestamp_to_scn,
      3    current_scn
      4  from v$database;
    
    CURRENT_TIMESTAMP              TIMESTAMP_TO_SCN CURRENT_SCN
    ------------------------------ ---------------- -----------
    16:48:11.477439                        10017141    10017179
    
    SQL> select current_timestamp,
      2    timestamp_to_scn(current_timestamp) timestamp_to_scn,
      3    current_scn
      4  from v$database;
    
    CURRENT_TIMESTAMP              TIMESTAMP_TO_SCN CURRENT_SCN
    ------------------------------ ---------------- -----------
    16:48:11.497542                        10017141    10017180
    
    SQL> select current_timestamp,
      2    timestamp_to_scn(current_timestamp) timestamp_to_scn,
      3    current_scn
      4  from v$database;
    
    CURRENT_TIMESTAMP              TIMESTAMP_TO_SCN CURRENT_SCN
    ------------------------------ ---------------- -----------
    16:48:11.537012                        10017141    10017181
    
    SQL> select current_timestamp,
      2    timestamp_to_scn(current_timestamp) timestamp_to_scn,
      3    current_scn
      4  from v$database;
    
    CURRENT_TIMESTAMP              TIMESTAMP_TO_SCN CURRENT_SCN
    ------------------------------ ---------------- -----------
    16:48:11.571872                        10017141    10017182
    

  5. SCN is a differnet kind of sequence number. It is composed of two things
    1. Timestamp 2. Sequence number. Since timestamp will always be unique the SCN will also be unique. Since timestamp portion is attached with it no need to worry that it will hit the maximum number.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>