Oracle Database

--
-- List database parameter history.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN snap_time FORMAT A20
COLUMN name FORMAT A20
COLUMN old_value FORMAT A20
COLUMN new_value FORMAT A20
COLUMN diff FORMAT A20
 
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 - &&V_NBR_DAYS) 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~'
and p.name='&&V_PARAM_NAME'
order by snap_time, instance_number
/