105 lines
4.4 KiB
SQL
105 lines
4.4 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
|
|
* --------- ------------ -----------------------------------------
|
|
* 02-Nov-13 Vishal Gupta Modified to use multiple value view
|
|
* (DBA_HIST_PARAMETER -> DBA_HIST_MVPARAMETER)
|
|
* 05-Jul-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
VARIABLE PARAM_NAME VARCHAR2(100)
|
|
VARIABLE INST_ID VARCHAR2(100)
|
|
VARIABLE DAYS VARCHAR2(100)
|
|
|
|
BEGIN
|
|
:PARAM_NAME:= '&&1';
|
|
:INST_ID := '&&2';
|
|
:DAYS := '&&3';
|
|
: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 a30
|
|
COLUMN value HEADING "Current Value" FORMAT a50
|
|
COLUMN prev_value HEADING "Previous Value" FORMAT a50
|
|
|
|
|
|
WITH snapshots
|
|
AS
|
|
(
|
|
SELECT s.dbid
|
|
, s.instance_number
|
|
, s.snap_id
|
|
, s.end_interval_time
|
|
, lag(s.snap_id) OVER (PARTITION BY s.dbid, s.instance_number
|
|
ORDER BY s.dbid, s.instance_number, s.snap_id) prev_snap_id
|
|
FROM v$database d
|
|
JOIN dba_hist_snapshot s ON s.dbid = d.dbid
|
|
WHERE s.end_interval_time > SYSDATE - :DAYS - 5/24 -- Check for 5 extra hours in past
|
|
AND s.instance_number LIKE :INST_ID
|
|
)
|
|
select TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI:SS') end_interval_time
|
|
, p.instance_number
|
|
, pn.parameter_name
|
|
--, p.ordinal
|
|
, p.VALUE
|
|
, prev.value prev_value
|
|
FROM snapshots s
|
|
JOIN dba_hist_parameter_name pn ON s.dbid = pn.dbid
|
|
-- multi value parameter view
|
|
LEFT OUTER JOIN dba_hist_mvparameter prev ON prev.dbid = s.dbid
|
|
AND prev.instance_number = s.instance_number
|
|
AND prev.snap_id = s.prev_snap_id
|
|
AND prev.parameter_hash = pn.parameter_hash
|
|
OUTER JOIN dba_hist_mvparameter p ON p.dbid = s.dbid
|
|
AND p.instance_number = s.instance_number
|
|
AND p.snap_id = s.snap_id
|
|
AND p.parameter_hash = pn.parameter_hash
|
|
AND NVL(p.value,'x') = NVL(prev.value,'x')
|
|
WHERE NVL(pn.parameter_name,'x') LIKE :PARAM_NAME
|
|
--AND REPLACE(pn.parameter_name,'_','!') NOT LIKE '!!%' -- Filter AMM or ASMM modified parameters if in AWR.
|
|
AND pn.parameter_name NOT LIKE '\_\_%' ESCAPE '\' -- Filter AMM or ASMM modified parameters if in AWR.
|
|
AND NVL(p.VALUE,'x') NOT LIKE '%SYS.KUPC$%' -- Ignore DataPump export/import service name change
|
|
AND NVL(p.value,'x') <> NVL(prev.value,'x')
|
|
ORDER BY TO_DATE(TO_CHAR(s.end_interval_time,'DD-MON-YY HH24:MI'),'DD-MON-YY HH24:MI')
|
|
, pn.parameter_name
|
|
, s.instance_number
|
|
;
|
|
|
|
@@footer |