Given any version of Oracle:
- How do I find my current SCN?
- What is the maximum possible SCN?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Given any version of Oracle:
SELECT dbms_flashback.get_system_change_number as current_scn FROM DUAL;
Oracle 10g and above:
SELECT current_scn FROM V$DATABASE;
SCN has a hard limit imposed by its format and a soft limit imposed artificially by Oracle, as described here. I've quoted the relevant portions below (emphasis added).
The architects of Oracle's flagship database application must have been well aware the SCN needed to be a massive integer. It is: a 48-bit number (281,474,976,710,656). It would take eons for an Oracle database to eclipse that number of transactions and cause problems -- or so you might think.
The soft limit derives from a very simple calculation anchored to a point in time 24 years ago: Take the number of seconds since 00:00:00 01/01/1988 and multiply that figure by 16,384. If the current SCN value is below that, then all is well and processing continues as normal. To put this in simple terms, the calculation assumes that a database running constantly since 01/01/1988, processing 16,384 transactions per second, cannot exist in reality.
This script (Oracle 10g and above) will check how much of the hard and soft limits you have exhausted. Thanks to Rob for calling out the soft limit.
WITH limits AS ( SELECT current_scn --, dbms_flashback.get_system_change_number as current_scn -- Oracle 9i , (SYSDATE - TO_DATE('1988-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) * 24*60*60 * 16384 AS SCN_soft_limit , 281474976710656 AS SCN_hard_limit FROM V$DATABASE ) SELECT current_scn , current_scn/scn_soft_limit*100 AS pct_soft_limit_exhausted , scn_soft_limit , current_scn/scn_hard_limit*100 AS pct_hard_limit_exhausted , scn_hard_limit FROM limits;
Here is a query I came up with to check my databases for sanity regarding the SCN bug issue:
# Show the amount of SCN keyspace we have used so far on this database # By default the SCN max on a 10g/11g # instance is a 48-bit integer (281,474,976,710,656) SELECT NAME, (current_scn/281474976710656)*100 as PCT_OF_SCN_KEYSPACE_USED, ROUND(SYSDATE-CREATED) as DAYS_SINCE_DB_CREATION, ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)) AS EST_DAYS_BEFORE_SCN_EXHAUSTED, ROUND(1/(current_scn/281474976710656)*(SYSDATE-CREATED)/365) AS EST_YEARS_BEFORE_SCN_EXHAUSTED FROM v$database;
Most of my databases that use DB links are at the 3.5% exhausted mark and can continue at the current rate for 50+ years without an issue. This does not mean I am safe from someone tickling the SCN bug, but at least we didn't find a database that was way higher than the others or close to the limit.