Check changed parameter history in oracle

Check changed parameter history in oracle

Check the history of parameter is changed in past in Oracle Database. If we are facing any specific issue like performance and we want to check any parameter is changed at database level in past few days then following ways help in it.

Following are the few ways to check the parameter changed in past:

1. Check the alert log file.

Search for the alter system command in alert log file.

2. If you have tuning pack then you can check from dba_hist_parameter table with following query.


Note: Table has snap id column, you can fetch time from dba_Hist_snapshot table by matching snap_id column

set linesize 155
col time for a15
col parameter_name format a50
col old_value format a30
col new_value format a30
break on instance skip 3
select instance_number instance, snap_id, time, parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('&parameter_name',parameter_name)
and a.instance_number like nvl('&instance_number',v.instance_number)
new_value != old_value
order by 1,2;

3. You can keep track of this changes by enabling audit on ALTER SYSTEM command

-- Enable the Audit
--Enable also for SYS user
ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE;
-- Restart the database for audit parameter in effect.
shutdown immediate
-- Enable the audit on ALTER SYSTEM privileges on user you want.

2 thoughts on “Check changed parameter history in oracle

  1. Anonymous

    I’d suggest you acknowlege the source of this query. I recognize the query in item two as something posted many years ago by an Oak Table member.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.