Laurent Schneider

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

This entry was posted on Monday, June 4th, 2007 at 11:35 and is filed under oracle, dba. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

5 Responses to “sequence / thread / system change number”

Edgar Says:
June 6th, 2007 at 13:43
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.
Laurent Schneider Says:
June 6th, 2007 at 16:48
no, I do not think so. Really big is multi-billions, which is incredibly small for Oracle
Edgar Says:
June 21st, 2007 at 18:44
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
Laurent Schneider Says:
June 26th, 2007 at 16:04
> 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

Laurent Schneider Says:
July 11th, 2007 at 15:48

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

Use <code> and </code> to post code

sequence / thread / system change number

5 Responses to “sequence / thread / system change number”

Leave a Reply