How do I find my current SCN?

Current SCN

Oracle 9i:

SELECT dbms_flashback.get_system_change_number as current_scn 
FROM DUAL;

Oracle 10g and above:

SELECT current_scn
FROM V$DATABASE;

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


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.

Tags:

Oracle

Recent Posts