0. 패키지 생성
|
패키지 생성 |
|
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;
|
1. 사용방법
|
쿼리를 이용한 방법 |
|
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 ; |