Checking the change in parameter happens in recent past

Checking the change in parameter happens in recent past . It check as per snap shots present.

col instance_number format 990 heading "SID"

col snap_id heading "Snap ID"

col name format a30 heading "Name"

col old_value format a30 heading "Old Value"

col new_value format a30 heading "New Value"

col diff format a15 heading "Numeric|Difference"

col instance_name new_value V_INSTANCE noprint

col snap_time format a20 heading "Snap Time"

set echo off feedback off timing off pagesize 100 linesize 180 trimout on trimspool on

select to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') snap_time,p.instance_number,p.snap_id,p.name,p.old_value,p.new_value,

decode(trim(translate(p.new_value, '0123456789', '          ')),'',trim(to_char(to_number(p.new_value) - to_number(p.old_value),'999999999999990')),'') diff

from (select dbid,

instance_number,

snap_id,

parameter_name name,

lag(trim(lower(value))) over(partition by dbid, instance_number, parameter_name order by snap_id) old_value,

trim(lower(value)) new_value,

decode(nvl(lag(trim(lower(value)))

over(partition by dbid,

instance_number,

parameter_name order by snap_id),

trim(lower(value))),

trim(lower(value)),

'~NO~CHANGE~',

trim(lower(value))) diff

from dba_hist_parameter) p,

dba_hist_snapshot s

where s.begin_interval_time between trunc(sysdate - 31) and

sysdate

and p.dbid = s.dbid

and p.instance_number = s.instance_number

and p.snap_id = s.snap_id

and p.diff <> '~NO~CHANGE~'

order by snap_time, instance_number

SQL> col instance_number format 990 heading "SID"

SQL> col snap_id heading "Snap ID"

SQL> col name format a30 heading "Name"

col old_value format a30 heading "Old Value"

SQL> SQL> col new_value format a30 heading "New Value"

SQL> col diff format a15 heading "Numeric|Difference"

SQL> col instance_name new_value V_INSTANCE noprint

SQL> col snap_time format a20 heading "Snap Time"

SQL> set echo off feedback off timing off pagesize 100 linesize 180 trimout on trimspool on

SQL> l

  1  select to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') snap_time,p.instance_number,p.snap_id,p.name,p.old_value,p.new_value,

  2  decode(trim(translate(p.new_value, '0123456789', '          ')),'',trim(to_char(to_number(p.new_value) - to_number(p.old_value),'999999999999990')),'') diff

  3  from (select dbid,

  4  instance_number,

  5  snap_id,

  6  parameter_name name,

  7  lag(trim(lower(value))) over(partition by dbid, instance_number, parameter_name order by snap_id) old_value,

  8  trim(lower(value)) new_value,

  9  decode(nvl(lag(trim(lower(value)))

10  over(partition by dbid,

11  instance_number,

12  parameter_name order by snap_id),

13  trim(lower(value))),

14  trim(lower(value)),

15  '~NO~CHANGE~',

16  trim(lower(value))) diff

17  from dba_hist_parameter) p,

18  dba_hist_snapshot s

19  where s.begin_interval_time between trunc(sysdate - 31) and

20  sysdate

21  and p.dbid = s.dbid

22  and p.instance_number = s.instance_number

23  and p.snap_id = s.snap_id

24  and p.diff <> '~NO~CHANGE~'

25* order by snap_time, instance_number

SQL> /

                                                                                                                                  Numeric

Snap Time             SID    Snap ID Name                           Old Value                      New Value                      Difference

-------------------- ---- ---------- ------------------------------ ------------------------------ ------------------------------ ---------------

08-APR-2012 08:00:16    1      22547 __db_cache_size                23622320128                    23488102400                    -134217728

08-APR-2012 08:00:16    1      22547 __shared_pool_size             2550136832                     2684354560                     134217728

08-APR-2012 10:00:42    1      22549 __shared_pool_size             2684354560                     2550136832                     -134217728

08-APR-2012 10:00:42    1      22549 __db_cache_size                23488102400                    23622320128                    134217728

09-APR-2012 12:00:12    1      22577 __db_cache_size                23622320128                    20937965568                    -2684354560

09-APR-2012 12:00:12    1      22577 shared_pool_size               0                              5234491392                     5234491392

09-APR-2012 12:00:12    1      22577 __shared_pool_size             2550136832                     5234491392                     2684354560