oracle sysaux full

• SYSAUX 사용현황

select occupant_name, space_usage_kbytes/1024 "MB"

from v$sysaux_occupants

order by space_usage_kbytes;

• SYSAUX SEGMENT 정보

select owner, segment_name, segment_type, bytes/1024/1024 "MB"

from dba_segments

where tablespace_name = 'SYSAUX'

order by bytes;

• 통계정보 보관주기 확인

select dbms_stats.get_stats_history_retention from dual;

• 보관주기 변경

exec dbms_stats.alter_stats_history_retention(8);

• 오래된 통계정보 삭제(15/10/10 이전 삭제 예시)

exec dbms_stats.purge_stats(to_timestamp_tz('10-10-2015 00:00:00 Asia/Seoul','DD-MM-YYYY HH24:MI:SS TZR'));

• AWR 현황

SELECT

  snap_id, begin_interval_time, end_interval_time

FROM

  SYS.WRM$_SNAPSHOT

WHERE

  snap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)

UNION

SELECT

  snap_id, begin_interval_time, end_interval_time

FROM

  SYS.WRM$_SNAPSHOT

WHERE

  snap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)

/

• AWR 삭제

BEGIN                                                              

  dbms_workload_repository.drop_snapshot_range(low_snap_id => 11169, high_snap_id=>11269);                                        

END;

/

• AWR 전체 삭제

@?/rdbms/admin/catnoawr.sql

@?/rdbms/admin/catawrtb.sql