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. Edgar

    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.

  3. Edgar

    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 🙂

  4. Laurent Schneider Post author

    > 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 👿

  5. Laurent Schneider Post author

    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

  6. Vinod

    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.

Comments are closed.