SCN

[sriniarvin]

Dear All,
I am having some doubts regarding the System Change
Number(SCN):
1. What is the minumum and maximum value of SCN?.
2. What will happen if the SCN reaches the maximum value ?. If it gets resetted to 0(or minimum), then how the read-consistency is maintained ?. Oracle determines the read-consistency and take the data for SQL queries from rollback segments based on the SCN value and hence how that mechanism works in this situation.

[pando]

these are discussed fully in Oracle Documentation, look at Concepts doco, locking and transaction management section

[julian]

I don't think there is a maximum but I've seen different starting/initial SCNs for different DBs.

The System Change Number is a unique identifier assigned at the time a transaction is commited and is not specific to distributed transactions.

[sriniarvin]

Hi Pando,
I have searched the document fully, however I could not get the answer for my questions. If you could post the exact documentation URL, it wud be very helpful.

Hi Julian,
Do U mean to say thet SCN can have an infinte value(!!!!).

[julian]

As you know, SCN, is a number internal to Oracle that is incremented over time as change vectors are generated, applied, and written to the Redo log.

There is also 'System Commit Number', also abbriviated SCN, which is a number also internal to Oracle that is incremented with each database COMMIT.

System Commit Numbers and System Change Numbers share the same internal sequence generator.

I do not know if this generator is cyclic.

[amittandon]

Dear All ,
It is clearly given in oracle docs and manuals that scn is set in a way that there is no end to scn generation.
regards
amit
its not cyclic in nature
its never ending

[amar]

1. What is the minumum and maximum value of SCN?.

I believe it starts with 0. About the maximum value - look at the next answer

2. What will happen if the SCN reaches the maximum value ?. If it gets resetted to 0(or minimum), ............

Quoting from the book "Oracle Backup and Recovery Handbook" by Rama Velpuri:

"SCN values never get reset to zero unless the database is re-created. You don't need to worry about running out of SCN's - even if you were to commit 16,000 transactions per second, it would take more than 500 years to run out of SCN numbers."

So, the maximum value of SCN would be atleast:

16000*60*60*24*365*500 = 252,288,000,000,000


-amar