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;










Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment