97 lines
3.8 KiB
SQL
97 lines
3.8 KiB
SQL
@@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 |