-------一个数据库当前最大的可能SCN被称为"最大合理SCN"，该值可以通过如下方式计算：
ora1a-> date
Tue Jun 24 12:15:19 GMT+08:00 2014
SQL> select
2 (
3 (
4 (
5 (
6 (
7 (
8 to_char(sysdate,'YYYY')-1988
9 )*12+
10 to_char(sysdate,'mm')-1
11 )*31+to_char(sysdate,'dd')-1
12 )*24+to_char(sysdate,'hh24')
13 )*60+to_char(sysdate,'mi')
14 )*60+to_char(sysdate,'ss')
15 ) * to_number('ffff','XXXXXXXX')/4 scn
16 from dual
17 /
SCN
----------
1.3944E+13
SQL> select to_char(1.3944E+13) from dual;
TO_CHAR(1.3944
--------------
13944000000000
-------------总共scn资源
SQL> select power(2,48) scn from dual;
SCN
----------
2.8147E+14
SQL> select to_char(2.8147E+14) from dual;
TO_CHAR(2.8147E
---------------
281470000000000
--------------当前数据库scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1.3944E+13
SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
13943693945867
-----------剩余数据库资源
SQL> select 281470000000000-13943693945867 from dual;
281470000000000-13943693945867
------------------------------
2.6753E+14
-------------Oracle在内部控制每秒增减的SCN不超过 16K，按照这样计算，当前数据库剩余517年
SQL> select 2.6753E+14 / 16 / 1024 / 3600 / 24 / 365 from dual;
2.6753E+14/16/1024/3600/24/365
------------------------------
517.780801
############################
在CPU补丁中，Oracle提供了一个脚本 scnhealthcheck.sql 用于检查数据库当前SCN的剩余情况。
该脚本的算法和以上描述相同，最终将最大合理SCN 减去当前数据库SCN，计算得出一个指标：HeadRoom。也就是SCN尚余的顶部空间，这个顶部空间最后折合成天数：
Rem
Rem $Header: rdbms/admin/scnhealthcheck.sql st_server_tbhukya_bug-13498243/8 2012/01/17 03:37:18 tbhukya Exp $
Rem
Rem scnhealthcheck.sql
Rem
Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved.
Rem
Rem NAME
Rem scnhealthcheck.sql - Scn Health check
Rem
Rem DESCRIPTION
Rem Checks scn health of a DB
Rem
Rem NOTES
Rem .
Rem
Rem MODIFIED (MM/DD/YY)
Rem tbhukya 01/11/12 - Created
Rem
Rem
define LOWTHRESHOLD=10
define MIDTHRESHOLD=62
define VERBOSE=FALSE
set veri off;
set feedback off;
set serverout on
DECLARE
verbose boolean:=&&VERBOSE;
BEGIN
For C in (
select
version,
date_time,
dbms_flashback.get_system_change_number current_scn,
indicator
from
(
select
version,
to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
((((
((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
(to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) +
(to_number(to_char(sysdate,'SS')))
) * (16*1024)) - dbms_flashback.get_system_change_number)
/ (16*1024*60*60*24)
) indicator
from v$instance
)
) LOOP
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
dbms_output.put_line( 'ScnHealthCheck' );
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
dbms_output.put_line( 'Current Date: '||C.date_time );
dbms_output.put_line( 'Current SCN: '||C.current_scn );
if (verbose) then
dbms_output.put_line( 'SCN Headroom: '||round(C.indicator,2) );
end if;
dbms_output.put_line( 'Version: '||C.version );
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
IF C.version > '10.2.0.5.0' and
C.version NOT LIKE '9.2%' THEN
IF C.indicator>&MIDTHRESHOLD THEN
dbms_output.put_line('Result: A - SCN Headroom is good');
dbms_output.put_line('Apply the latest recommended patches');
dbms_output.put_line('based on your maintenance schedule');
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
|| '24 after apply.');
END IF;
ELSIF C.indicator<=&LOWTHRESHOLD THEN
dbms_output.put_line('Result: C - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now' );
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('set _external_scn_rejection_threshold_hours=24 '
|| 'after apply');
END IF;
dbms_output.put_line('AND contact Oracle support immediately.' );
ELSE
dbms_output.put_line('Result: B - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now');
IF (C.version < '11.2.0.2') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours='
||'24 after apply.');
END IF;
END IF;
ELSE
IF C.indicator<=&MIDTHRESHOLD THEN
dbms_output.put_line('Result: C - SCN Headroom is low');
dbms_output.put_line('If you have not already done so apply' );
dbms_output.put_line('the latest recommended patches right now' );
IF (C.version >= '10.1.0.5.0' and
C.version <= '10.2.0.5.0' and
C.version NOT LIKE '9.2%') THEN
dbms_output.put_line(', set _external_scn_rejection_threshold_hours=24'
|| ' after apply');
END IF;
dbms_output.put_line('AND contact Oracle support immediately.' );
ELSE
dbms_output.put_line('Result: A - SCN Headroom is good');
dbms_output.put_line('Apply the latest recommended patches');
dbms_output.put_line('based on your maintenance schedule ');
IF (C.version >= '10.1.0.5.0' and
C.version <= '10.2.0.5.0' and
C.version NOT LIKE '9.2%') THEN
dbms_output.put_line('AND set _external_scn_rejection_threshold_hours=24'
|| ' after apply.');
END IF;
END IF;
END IF;
dbms_output.put_line(
'For further information review MOS document id 1393363.1');
dbms_output.put_line( '-----------------------------------------------------'
|| '---------' );
END LOOP;
end;
/
--------------------------------------------------------------
ScnHealthCheck
--------------------------------------------------------------
Current Date: 2014/06/24 13:43:04
Current SCN: 973822
Version: 11.2.0.1.0
--------------------------------------------------------------
Result: A - SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
AND set _external_scn_rejection_threshold_hours=24 after apply.
For further information review MOS document id 1393363.1
--------------------------------------------------------------

oracle-scn资源控制