|
 |
| |
|
|
최근(AWR 보관주기내) 변경된 파라미터 추출(10g 이상)
|
|
park1q
|
2018-10-19 10:44:42, 조회 : 7,177, 추천 : 3819 |
- 최근 변경된 파라미터와 그 변경 전/후의 값을 추출한다.
select snap_id,
to_char(end_interval_time,'yyyy/mm/dd hh24:mi:ss') end_interval_time ,
parameter_name,
bef_value,
value cur_value,
isdefault,
ismodified
from (select p.snap_id,
s.end_interval_time,
p.parameter_name,
lag(p.value,1) over(partition by p.instance_number , p.parameter_name order by p.snap_id ) bef_value,
p.value,
p.isdefault,
p.ismodified,
min(p.snap_id) over() min_snap_id
from dba_hist_parameter p,
(select dbid,
instance_number,
max(a.end_interval_time) end_interval_time,
max(snap_id) last_snap_id
from dba_hist_snapshot a
where dbid = (select dbid from v$database)
and instance_number = (select instance_number from v$instance)
group by dbid, instance_number, trunc(end_interval_time)) s
where p.dbid = s.dbid
and p.instance_number = s.instance_number
and p.snap_id = s.last_snap_id
)
where snap_id != min_snap_id
and (value != bef_value or (bef_value is null and value is not null))
SNAP_ID END_INTERVAL_TIME PARAMETER_NAME BEF_VALUE CUR_VALUE ISDEFAULT ISMODIFI
---------- ------------------------ ------------------------ ----------- ----------- --------- --------
335 10/09/06 15:00:26.390 fixed_date NONE TRUE MODIFIED
360 10/09/09 09:11:32.390 _optim_peek_user_binds FALSE TRUE MODIFIED
|
|
|
|
|
|
|
|
Technote
