Query to Show Parameter Change History with Time
SELECT
p1.snap_id,
TO_CHAR(s.begin_interval_time, 'DD-MON-YYYY HH24:MI:SS') AS begin_time,
TO_CHAR(s.end_interval_time, 'DD-MON-YYYY HH24:MI:SS') AS end_time,
p1.instance_number,
p1.name,
p1.value AS old_value,
p2.value AS new_value,
p2.isdefault,
p2.ismodified
FROM
dba_hist_parameter p1
JOIN
dba_hist_parameter p2
ON p1.snap_id = p2.snap_id - 1
AND p1.name = p2.name
AND p1.instance_number = p2.instance_number
JOIN
dba_hist_snapshot s
ON p1.snap_id = s.snap_id
AND p1.dbid = s.dbid
WHERE
p1.value IS DISTINCT FROM p2.value
ORDER BY
p1.name, p1.snap_id;
Post a Comment
Post a Comment