Given any version of Oracle:

  • How do I find my current SCN?
  • What is the maximum possible SCN?

3 Answers 3


Current SCN

Oracle 9i:

SELECT dbms_flashback.get_system_change_number as current_scn 

Oracle 10g and above:

SELECT current_scn

SCN Limits

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

Hard Limit

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.

Soft Limit

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.

SCN Limit Check

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 (
  --, 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
  , 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) 
   (current_scn/281474976710656)*100 as PCT_OF_SCN_KEYSPACE_USED,  
   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.


281,474,976,710,656 is the hard limit. You'll want to know what the soft limit is, since that is the value you'd hit your head on first. The soft limit is (roughly) calculated by the number of seconds elapsed since Jan 1, 1988 x 16384.

  • I don't know how the old answers missed the soft limit (which is mentioned in the article linked by Nick) - so it is a good idea for adding the missing detail. Jun 20, 2013 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.