멋지게 놀아라

패키지 생성

CREATE OR REPLACE PACKAGE SPACE_CHECK

AS

    TYPE return_row IS RECORD (

             segment_owner  varchar2(30) ,

             segment_name varchar2(81) ,

             segment_type varchar2(19) ,

             segment_space_management varchar2(6) ,

             total_blocks number,

             total_bytes number,

             unused_blocks number,

             unused_bytes number,

             last_used_extent_file_id number,

             last_used_extent_block_id number,

             last_used_block number,

             unformatted_blocks  number,

             unformatted_bytes  number,

             fs1_blocks  number,

             fs1_bytes   number,

             fs2_blocks  number,

             fs2_bytes   number,

             fs3_blocks  number,

             fs3_bytes   number,

             fs4_blocks  number,

             fs4_bytes   number,

             full_blocks  number,

             full_bytes  number ) ;

     TYPE return_table IS TABLE OF return_row ;

     FUNCTION F_SPACE_USAGE(i_owner varchar2)

     RETURN return_table

     PIPELINED;

END SPACE_CHECK ;

패키지 Body 생성

CREATE OR REPLACE PACKAGE BODY SPACE_CHECK AS

    FUNCTION F_SPACE_USAGE(i_owner varchar2)

        RETURN return_table

        PIPELINED IS

        rec            return_row;

        -- common

        v_segment_owner  varchar2(30) ;

        v_segment_name varchar2(81) ;

        v_segment_type varchar2(19) ;

        v_management varchar2(6) ;

        -- unused_space

        v_total_blocks number(22) ;

        v_total_bytes number(22) ;

        v_unused_blocks number(22) ;

        v_unused_bytes number(22) ;

        v_last_used_extent_file_id number(22) ;

        v_last_used_extent_block_id number(22) ;

        v_last_used_block number(22) ;

        v_partition_name varchar2(30) DEFAULT NULL ;

        -- space_usage

        v_unformatted_blocks  number;

        v_unformatted_bytes  number;

        v_fs1_blocks  number;

        v_fs1_bytes   number;

        v_fs2_blocks  number;

        v_fs2_bytes   number;

        v_fs3_blocks  number;

        v_fs3_bytes   number;

        v_fs4_blocks  number;

        v_fs4_bytes   number;

        v_full_blocks  number;

        v_full_bytes  number;                

    BEGIN

    FOR v_for IN ( SELECT sm.owner , sm.segment_name , sm.segment_type , ts.segment_space_management

                   FROM dba_segments sm , dba_tablespaces ts

                   WHERE owner = i_owner

                   AND segment_type IN ('TABLE','INDEX')

                   AND sm.tablespace_name = ts.tablespace_name ) LOOP

          v_segment_owner := v_for.owner ;

          v_segment_name := v_for.segment_name ;

          v_segment_type := v_for.segment_type        ;        

          v_management := v_for.segment_space_management ;                        

          --exec dbms_space package

          dbms_space.unused_space (  v_segment_owner

                                   , v_segment_name

                                             , v_segment_type

                                             , v_total_blocks

                                             , v_total_bytes

                                             , v_unused_blocks

                                             , v_unused_bytes

                                             , v_last_used_extent_file_id

                                             , v_last_used_extent_block_id

                                             , v_last_used_block

                                   , v_partition_name ) ;        

             rec.segment_owner :=  v_segment_owner ;          

             rec.segment_name := v_segment_name ;

             rec.segment_type := v_segment_type ;

             rec.segment_space_management := v_management ;

             rec.total_blocks := v_total_blocks ;

             rec.total_bytes := v_total_bytes ;

             rec.unused_blocks := v_unused_blocks ;

             rec.unused_bytes := v_unused_bytes ;

             rec.last_used_extent_file_id := v_last_used_extent_file_id ;

             rec.last_used_extent_block_id := v_last_used_extent_block_id ;

             rec.last_used_block := v_last_used_block ;                        

       IF         v_management = 'AUTO' THEN                        

             dbms_space.space_usage ( v_segment_owner

                                    , v_segment_name

                                    , v_segment_type

                                    , v_unformatted_blocks 

                                    , v_unformatted_bytes 

                                    , v_fs1_blocks 

                                    , v_fs1_bytes  

                                    , v_fs2_blocks 

                                    , v_fs2_bytes  

                                    , v_fs3_blocks 

                                    , v_fs3_bytes  

                                    , v_fs4_blocks 

                                    , v_fs4_bytes  

                                    , v_full_blocks 

                                    , v_full_bytes) ;        

                  rec.unformatted_blocks := v_unformatted_blocks  ;

                  rec.unformatted_bytes := v_unformatted_bytes  ;

                  rec.fs1_blocks := v_fs1_blocks  ;

                  rec.fs1_bytes := v_fs1_bytes   ;

                  rec.fs2_blocks := v_fs2_blocks  ;

                  rec.fs2_bytes := v_fs2_bytes   ;

                  rec.fs3_blocks := v_fs3_blocks  ;

                  rec.fs3_bytes :=  v_fs3_bytes   ;

                  rec.fs4_blocks := v_fs4_blocks  ;

                  rec.fs4_bytes := v_fs4_bytes   ;

                  rec.full_blocks := v_full_blocks  ;

                  rec.full_bytes := v_full_bytes  ;

      ELSE

                  rec.unformatted_blocks := 0  ;

                  rec.unformatted_bytes := 0  ;

                  rec.fs1_blocks := 0  ;

                  rec.fs1_bytes := 0   ;

                  rec.fs2_blocks := 0  ;

                  rec.fs2_bytes := 0   ;

                  rec.fs3_blocks := 0  ;

                  rec.fs3_bytes :=  0   ;

                  rec.fs4_blocks := 0  ;

                  rec.fs4_bytes := 0   ;

                  rec.full_blocks := 0  ;

                  rec.full_bytes := 0  ;

      END IF ;

   PIPE ROW (rec);

END LOOP ;

RETURN;

    END F_SPACE_USAGE ;

END;

쿼리를 이용한 방법

col segment_name format a24 heading "Seg|Name"

col segment_owner format a8 heading "Seg|Owner"

col segment_type format a6 heading "Seg|Type"

col total_blocks format 99,999,999 heading "Total|Blocks"

col total_bytes format 99,999,999,999 heading "Total|Byte"

col fs1_blocks format 999,999 heading " 0~25|Blocks"

col fs1_bytes format 999,999 heading "0~25 |Byte"

col fs2_blocks format 99,999,999 heading "25~50|Block"

col fs2_bytes format 99,999,999,999 heading "25~50|Byte"

col fs3_blocks format 99,999,999 heading "50~75|Blocks"

col fs3_bytes format 99,999,999,999 heading "50~75|Byte"

col fs4_blocks format 99,999,999 heading "75~100|Blocks"

col fs4_bytes format 99,999,999,999 heading "75~100|Byte"

col full_blocks format 99,999,999 heading "Full|Blocks"

col full_bytes format 99,999,999,999 heading "Full|Byte"

col free_sum_blocks format 99,999,999 heading "FreeSum|Blocks"

col expected_bytes format 99,999,999 heading "Expected|Free Bytes"

col free_sum_bytes format 99,999,999,999 heading "FreeSum|Byte"

col free_block_percent format 999.9 heading "Free|%"

set pagesize 300

set line 300

select segment_name ,

       segment_owner ,

   segment_type ,

       total_blocks ,

       total_bytes ,

       fs1_blocks ,

       fs1_bytes ,

       fs2_blocks ,

       fs2_bytes ,

       fs3_blocks ,

       fs3_bytes ,

       fs4_blocks ,

       fs4_bytes ,

       full_blocks ,

       full_bytes ,

   (fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) free_sum_blocks ,

   (fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes) free_sum_bytes ,

   ((fs1_bytes+fs4_bytes)/2 + (fs2_bytes + fs3_bytes)/2) expected_bytes,

   ((fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) / total_blocks) * 100 free_block_percent

from table(space_check.f_space_usage('US_MAXG'))

where fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks > 1000

order by (fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) desc ;

엑셀에서 사용하기 쉽게

col space_check heading "NAME/OWNER/TYPE/T_BLK/T_BT/FS1_BLK/FS1_BT/FS2_BLK/FS2_BT/FS3_BLK/ -

FS3_BT/FS4_BLK/FS4_BT/FULL_BLK/FULL_BT/FREE_SUM_BLK/FREE_SUM_BT/EXPECTED_BT/FREE_BLK_PCT"

select segment_name || '/' ||

       segment_owner || '/' ||

  segment_type || '/' ||

       to_char(total_blocks) || '/' ||

       to_char(total_bytes) || '/' ||

       to_char(fs1_blocks) || '/' ||

       to_char(fs1_bytes) || '/' ||

       to_char(fs2_blocks) || '/' ||

       to_char(fs2_bytes) || '/' ||

       to_char(fs3_blocks) || '/' ||

       to_char(fs3_bytes) || '/' ||

       to_char(fs4_blocks) || '/' ||

       to_char(fs4_bytes) || '/' ||

       to_char(full_blocks) || '/' ||

       to_char(full_bytes) || '/' ||

  to_char((fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks))|| '/' ||

  to_char((fs1_bytes + fs2_bytes + fs3_bytes + fs4_bytes))|| '/' ||

  to_char(((fs1_bytes+fs4_bytes)/2 + (fs2_bytes + fs3_bytes)/2))|| '/' ||

  to_char(((fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) / total_blocks) * 100 ) SPACE_CHECK

from table(space_check.f_space_usage('US_MAXG'))

where fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks > 1000

order by (fs1_blocks + fs2_blocks + fs3_blocks + fs4_blocks) desc ;