www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   회원:0명 / 손님:0명
 

 

Technote

자료 및 관리팁

  • DBA Notes
  • Q & A

    북마크
  • Asktom
       (Oracle 의 대표 Forum)
  • Technical Bulltin(KR)
       (기술지원게시판)
  • Dbazine

  •  

     


     최근(AWR 보관주기내) 변경된 파라미터 추출(10g 이상)
    park1q  2010-09-09 10:24:24, 조회 : 5,872, 추천 : 1114

    • 최근 변경된 파라미터와 그 변경 전/후의 값을 추출한다.

     
    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


      추천하기 프린트   목록보기

    Copyright 1999-2018 Zeroboard

     

     
     
    [Today:10 / Total:154205]    Design by p@rk1q