개요

Reorg 대상 테이블 조회

테이블에 대량의 DML이 발생한 경우, 공간 재활용 및 조회 성능 개선을 위해 Reorg가 필요할 수 있습니다.

Reorg 대상 테이블은 생성된 통계 정보를 기반으로 선정할 수도 있으나, 통계 정보만으로는 정확한 사용량을 측정하기 어렵습니다.

정확한 사용량 확인을 위해서는 DBMS_SPACE 패키지를 활용할 수 있습니다.

방법

쿼리

SET serveroutput ON
SET PAGESIZE 100
SET LINESIZE 100

ACCEPT owner PROMPT 'Enter Owner : '
variable owner       varchar2(100)

DECLARE
       USED_BLOCKS NUMBER;
       FRM_BLOCKS NUMBER;
       FRM_RATIO NUMBER;
       uf   NUMBER;
       ub   NUMBER;
       f1   NUMBER;
       f1b  NUMBER;
       f2   NUMBER;
       f2b  NUMBER;
       f3   NUMBER;
       f3b  NUMBER;
       f4   NUMBER;
       f4b  NUMBER;
       fbl  NUMBER;
       fby  NUMBER;
BEGIN
       :owner := upper('&owner');
       FOR tb_list in( SELECT OWNER,TABLE_NAME
                         FROM DBA_TABLES
                        WHERE OWNER = :owner
                       ) LOOP
               BEGIN
                       dbms_space.space_usage(tb_list.owner, tb_list.table_name,'TABLE',uf,ub,f1,f1b,f2,f2b,f3,f3b,f4,f4b,fbl,fby);
                       USED_BLOCKS  :=  f1 + f2 + f3 + f4 + fbl;
                       FRM_BLOCKS := f3+f4;
                       IF USED_BLOCKS = 0 THEN
                               dbms_output.put_line('----------------------');
                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' is not used.');
                       ELSE
                               FRM_RATIO:= ROUND(FRM_BLOCKS/ USED_BLOCKS*100);
                               IF USED_BLOCKS>100 THEN
                                       IF FRM_RATIO> 20 THEN  -- reoge 대상 테이블 단편화 수치 20% 이상으로 지정 (변경가능)
                                               dbms_output.put_line('#######################');
                                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' is fragmented '||FRM_RATIO||'%');
                                               dbms_output.put_line('unformatted blocks:  ' || TO_CHAR(uf));
                                               dbms_output.put_line('blocks 0-25% free:   ' || TO_CHAR(f1));
                                               dbms_output.put_line('blocks 25-50% free:  ' || TO_CHAR(f2));
                                               dbms_output.put_line('blocks 50-75% free:  ' || TO_CHAR(f3));
                                               dbms_output.put_line('blocks 75-100% free: ' || TO_CHAR(f4));
                                               dbms_output.put_line('full blocks:         ' || TO_CHAR(fbl));
                                       ELSE
                                               dbms_output.put_line('----------------------');
                                               dbms_output.put_line(tb_list.owner||'.'||tb_list.table_name||' pass ['|| FRM_RATIO ||'%]');
                                       End If;
                               END IF;
                       END IF;
               EXCEPTION
               WHEN OTHERS THEN
                       dbms_output.put_line( TO_CHAR (SQLCODE) || ':' || SQLERRM);
               END;
       END LOOP;
END;
/