10g이상 지원 shrink할 대상 찾는 function

제목: 10g 지원 shrink할 대상 찾는 function..
 

================================================================================

++++++++++++++++
특징
++++++++++++++++
10g부터 Shrink 라는 것이 지원됩니다. Table의 데이타를 reorg하듯이 진행하는데,
rebuild와 다른점은, 데이타를 내부적으로 insert, delete를 합니다. 그런데, table중에
어떤것을 shrink하게 해야 할지 파악하기 어려운데, oracle에서 지원되는 아래 procedure를
사용하여 파악할수 있읍니다.

==> dbms_space.verify_shrink_candidate


[Example]

begin
dbms_output.put_line('Checking shrink table.....');
dbms_output.put_line('Candidated tables which will be save more then 50% size');
FOR sel_c_r IN
   ( select owner, segment_name, bytes, partition_name,
            decode(partition_name,null,null,'.'||partition_name) is_part,
            tablespace_name
 from dba_segments
 where segment_type= 'TABLE' and owner not in ('SYS','SYSTEM')
   )
LOOP
   if (dbms_space.verify_shrink_candidate(sel_c_r.owner,
                                          sel_c_r.segment_name,
                                          'TABLE',
                                    --    sel_c_r.bytes/10,  -- 10%의 효과개선될..
                                          sel_c_r.bytes/50,  -- 50%의 효과개선될..
                                          sel_c_r.partition_name ))
   then
         dbms_output.put_line(' ==> '||sel_c_r.owner||'.'||sel_c_r.segment_name
         || sel_c_r.is_part||', '||sel_c_r.bytes/1024/1024
         ||'M ,  TS NAME :'||sel_c_r.tablespace_name);

   end if;
end loop;
end;
/

  Checking shrink table.....
Candidated tables which will be save more then 50% size
==> TEST.TEST, 128M ,  TS NAME :TOOLS2

PL/SQL 처리가 정상적으로 완료되었습니다.

반응형

■FUNCTION VERIFY_SHRINK_CANDIDATE RETURNS BOOLEAN

 인수명                         유형                    기본 내부/외부?
 ------------------------------ ----------------------- --------- --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 SHRINK_TARGET_BYTES            NUMBER                  IN
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT