System Change Number or SCN is internal timestamp used by Oracle database to comply with ACID properties of database. Every transaction in the database is assigned a (unique) SCN value. Generally the SCN values assigned for each transaction is supposed to be unique but quite often we have noticed transactions with duplicate SCN values mostly under high concurrency.
.
Oracle SCN is incremented by 16384 for every second since 1/1/1988. This number is also called soft Limit and increase every second. The maximum value or hard limit of SCN supported by Oracle is 281 trillion, good enough to last 500 years. At any point in time, the database SCN cannot be greater than SCN Soft limit. This might temporarily make the database unavailable or may cause the database to crash with ORA-600 errors. The database will start to function normally as you start to accumulate SCNs with time. Without any bugs, it is possible but rare to use up 16384 SCN’s in 1 second. In unusual circumstances and if required, you can also fool the database by changing the system date to sometime in future but this may introduce data issues.
.
SCN soft limit can be calculated with following query
.
COLUMN SCN FORMAT 999999999999999999999999999
SELECT (SYSDATE – TO_DATE(’01-JAN-1988′)) * 24*60*60 *16384 AS SCN FROM DUAL;
.
Here is simple example to understand working of SCN and for sake of simplicity , lets assume the current Value of SCN at 10:00:01 AM is X.
.
Oracle SCN is incremented by 16384 for every second since 1/1/1988. This number is also called soft Limit and increase every second. The maximum value or hard limit of SCN supported by Oracle is 281 trillion, good enough to last 500 years. At any point in time, the database SCN cannot be greater than SCN Soft limit. This might temporarily make the database unavailable or may cause the database to crash with ORA-600 errors. The database will start to function normally as you start to accumulate SCNs with time. Without any bugs, it is possible but rare to use up 16384 SCN’s in 1 second. In unusual circumstances and if required, you can also fool the database by changing the system date to sometime in future but this may introduce data issues.
.
SCN soft limit can be calculated with following query
.
COLUMN SCN FORMAT 999999999999999999999999999
SELECT (SYSDATE – TO_DATE(’01-JAN-1988′)) * 24*60*60 *16384 AS SCN FROM DUAL;
.
Here is simple example to understand working of SCN and for sake of simplicity , lets assume the current Value of SCN at 10:00:01 AM is X.
No comments:
Post a Comment