1. 스냅샷 설정 확인
select * from dba_hist_wr_control ;
2. 현재 스냅샷 조회
col startup_time for a30
col begin_interval_time for a30
select snap_id, startup_time, begin_interval_time, snap_level from dba_hist_snapshot order by 3 ;
3. SYSAUX 사용량 조회
select df.tablespace_name “Tablespace”,
round(df.TBS_byte /1048576,2) “Total(MB)”,
round((df.TBS_byte – fs.Free_byte)/1048576,2) “Used(MB)”,
round(fs.Free_byte /1048576,2) “Free(MB)”,
round((fs.Free_byte/df.TBS_byte) *100,0) “Free(%)”,
fs.pieces “Pieces”,
round(fs.Max_free /1048576,2) “MaxFree(MB)”,
db.EXTENT_MANAGEMENT
from ( select tablespace_name, sum(bytes) TBS_byte
from dba_data_files group by tablespace_name ) df,
( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces
from dba_free_space group by tablespace_name ) fs,
( select tablespace_name, initial_extent, next_extent,EXTENT_MANAGEMENT
from dba_tablespaces ) db
where df.tablespace_name = db.tablespace_name
and df.tablespace_name = fs.tablespace_name(+)
and df.tablespace_name = ‘SYSAUX’
order by 5
/
4. v$SYSAUX_OCCUPANTS 조회
col OCCUPANT_NAME for a30
col SCHEMA_NAME for a20
select OCCUPANT_NAME,SCHEMA_NAME,SPACE_USAGE_KBYTES from v$sysaux_occupants order by 3 ;
5. SYSAUX SEGMENT 조회
col OWNER for a20
col SEGMENT_NAME for a30
col SEGMENT_TYPE for a20
col TABLESPACE_NAME for a20
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 “MB” from dba_segments where tablespace_name=’SYSAUX’ order by 6 ;
##select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 “MB” from dba_segments where tablespace_name=’SYS1′ order by 6 ;
6. 스냅샷 min, max 조회
select min(snap_id), max(snap_id) from WRM$_SNAPSHOT ;
7. 스냅샷이 없는 orphaned 행 조회
##select count(*) From WRH$_LATCH_CHILDREN where snap_id < 842 ;
select count(*) From WRH$_LATCH_CHILDREN where snap_id < min(snap_id) ;
8. orphaned 삭제, 사이즈 체크
DELETE
FROM WRH$_LATCH_CHILDREN a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
9. 테이블 쉬링크, 사이즈 체크
alter table WRH$_LATCH_CHILDREN shrink space ;
alter index WRH$_LATCH_CHILDREN_PK rebuild partition ‘partition_name’ ;
10. 확인
select min(snap_id), max(snap_id) from WRM$_SNAPSHOT ;
select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 “MB” from dba_segments where tablespace_name=’SYSAUX’ order by 6 ;