Take the 2-minute tour ×
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's 100% free, no registration required.

Given any version of Oracle:

  • How do I find my current SCN?
  • What is the maximum possible SCN?
share|improve this question
    
This question was inspired by this article. There are probably a lot of Oracle people looking up these numbers right now. :) –  Nick Chammas Jan 23 '12 at 17:50
add comment

3 Answers

up vote 7 down vote accepted

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;
share|improve this answer
1  
I see you have your answer. You should also read Riyaj's article at orainternals.com/2012/01/20/scn-what-why-and-how –  Niall Litchfield Jan 25 '12 at 7:41
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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. –  dezso Jun 20 '13 at 20:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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