@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display database parameter changes from AWR Repository
*  Parameters : 1 - Parameter Name          (Use % as wild card and \ as escape character)
*               2 - Instance Number         ( Use % to query for all instances)
*               3 - Number of days to query ( Default 30)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Jul-12  Vishal Gupta  Created
*  
*/



/************************************
*  INPUT PARAMETERS
************************************/
VARIABLE PARAM_NAME   VARCHAR2(100)
VARIABLE INST_ID      VARCHAR2(100)
VARIABLE DAYS         VARCHAR2(100)
VARIABLE WHERECLAUSE  VARCHAR2(4000)

BEGIN
     :PARAM_NAME:= '&&1';
     :INST_ID := '&&2';
     :DAYS := '&&3';
     :WHERECLAUSE := '&&4';
     :PARAM_NAME := CASE WHEN NVL(:PARAM_NAME,'x') = 'x' THEN '%' ELSE :PARAM_NAME END;
     :INST_ID := CASE WHEN NVL(:INST_ID,'x') = 'x' THEN '%' ELSE :INST_ID END;
     :DAYS := CASE WHEN NVL(:DAYS,'x') = 'x' THEN '7' ELSE :DAYS END;
END;
/


set head off
SELECT '**********************************************'  || chr(10) ||
       '* init.ora Parameter Change History'             || chr(10) ||
       '* (From AWR Repository)'                         || chr(10) ||
       '*'                                               || chr(10) ||
       '* Input Parameters'                              || chr(10) ||
       '*  - Parameter Name = "' || :PARAM_NAME || '"'   || chr(10) ||
       '*  - Instance#      = "' || :INST_ID || '"'      || chr(10) ||
       '*  - Days           = "' || :DAYS || '"'         || chr(10) ||
       '**********************************************'  || chr(10) ||
       ''
FROM DUAL;       
set head on


COLUMN end_interval_time    HEADING "Snapshot Time"         FORMAT a18
COLUMN instance_number      HEADING "I#"                    FORMAT 99 
COLUMN parameter_name       HEADING "ParameterName"         FORMAT a31
COLUMN value                HEADING "Current Value"         FORMAT a50
COLUMN prev_value           HEADING "Previous Value"        FORMAT a50


select TO_CHAR(a.end_interval_time,'DD-MON-YY HH24:MI:SS') end_interval_time
     , a.instance_number
     , a.parameter_name
     --, a.ordinal
     , a.value
     , a.prev_value
FROM
 (SELECT /*+ LEADING(s pn) parallel(s,4) parallel(pn,4) */
         s.end_interval_time 
       , p.instance_number
       , p.parameter_name
       , p.VALUE
       , lag(p.VALUE) OVER (PARTITION BY p.dbid, p.instance_number, p.parameter_hash
                            ORDER BY p.dbid, p.snap_id, p.instance_number, p.parameter_hash) prev_value
  FROM v$database d
       JOIN dba_hist_snapshot s ON s.dbid = d.dbid  
       JOIN dba_hist_parameter p ON p.dbid = s.dbid AND p.snap_id = s.snap_id AND p.instance_number = s.instance_number  
  WHERE 1=1 
    AND s.instance_number LIKE :INST_ID
    AND s.end_interval_time > SYSDATE - :DAYS - (5/24) -- Check for 5 extra hours in past
    AND p.parameter_name  LIKE  :PARAM_NAME  
    --AND REPLACE(p.parameter_name,'_','!') NOT LIKE '!!%'  -- Filter AMM or ASMM modified parameters if in AWR.
    AND p.parameter_name NOT LIKE '\_\_%' ESCAPE '\' -- Filter AMM or ASMM modified parameters if in AWR.
    AND p.parameter_name NOT IN ('service_names')
    AND NVL(p.VALUE,'x')  NOT LIKE '%SYS.KUPC$%'  -- Ignore DataPump export/import service name change
  ) a
WHERE 1=1
  AND LOWER(NVL(a.value,'x')) <> LOWER(NVL(a.prev_value,'x'))
  AND a.end_interval_time > sysdate - :DAYS 
ORDER BY TO_DATE(TO_CHAR(a.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI') 
       , a.parameter_name
       , a.instance_number
;

@@footer