단편화된 Table 정보 찾기

2014. 2. 13. 16:28
 
 
How to get a list of most fragmented tables in Oracle Database 
  • Table 단편화 정보 조회 쿼리 
select table_name,round((blocks*8),2) "size (kb)" , 
                            round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                            (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc

결과는 아래와 같은 형태이다. 

TABLE NAME     SIZE     ACTUAL DATA     WASTER SPACE
TREE     0     0     0
GC_S     3744     4651.9     -907.9
TRAIL     104     113.04     -9.04
ASSOCIATION_RULES     272     353     -81
ATTRIBUTES     1728     2528.12     -800.12
AUDITACTION     128     208.48     -80.48
DV     18608     36266.47     -17658.47
S134     728     903.08     -175.08
A178     344     518.75     -174.75
S129     728     896.48     -168.48
AGS_NODES     2864     4510.33     -1646.33
S149     472     633.79     -161.79
S127     728     871.62     -143.62
tu     2232     3619.76     -1387.76
PCd_DATA     3112     4371.75     -1259.75


단편화된(Fragmented) 테이블에 대해서는 내용을 파악하여 reorg 등 적절한 조치를 취하도록 한다. 


참조 :  https://community.oracle.com/thread/1106205?start=0&tstart=0


슈플 Oracle/Administration