SYSAUX Table Shrink 방법

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 

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

/

참조 :  http://haisins.epac.to/wordpress/?p=764 |작성자 박용석