new_show_space.sql.zip

REMREMbasedonpreviousshow_spacescript,nowitcanREMidentifyallpartition_namefortableorindex-REMautomaticallywithoutspecifyingpartition_name.REMREMUsage:REMexecshow_space('TABLE','OWNER','TABLE_NAME');REMorREMexecshow_space('TABLEPARTITION','TEST','P_TAB','PART1');REMREMexecshow_space('INDEX','TEST','IDX_TAB');REMorREMexecshow_space('INDEXPARTITION','TEST','IDX_TAB','IDX_PART1');REMREMEditedbymxat2020/03/27REM--basedonpreviousprocedureshow_spacefromInternet.setserveroutputonCREATEORREPLACEPROCEDUREshow_space(v_segment_typeINVARCHAR2DEFAULT'TABLE',v_segment_ownerINVARCHAR2DEFAULTUSER,v_segment_nameINVARCHAR2,v_partition_nameINVARCHAR2DEFAULTNULL,v_spaceINVARCHAR2DEFAULT'AUTO',v_analyzedINVARCHAR2DEFAULT'Y')ASp_segment_typeVARCHAR2(30);p_segment_ownerVARCHAR2(30);p_segment_nameVARCHAR2(50);p_partition_nameVARCHAR2(30);p_partitionedVARCHAR2(5);l_unformatted_blocksNUMBER;l_unformatted_bytesNUMBER;l_fs1_blocksNUMBER;l_fs1_bytesNUMBER;l_fs2_blocksNUMBER;l_fs2_bytesNUMBER;l_fs3_blocksNUMBER;l_fs3_bytesNUMBER;l_fs4_blocksNUMBER;l_fs4_bytesNUMBER;l_full_blocksNUMBER;l_full_bytesNUMBER;l_free_blksNUMBER;l_total_blocksNUMBER;l_total_bytesNUMBER;l_unused_blocksNUMBER;l_unused_bytesNUMBER;l_LastUsedExtFileIdNUMBER;l_LastUsedExtBlockIdNUMBER;l_LAST_USED_BLOCKNUMBER;PROCEDUREprint(p_labelINVARCHAR2,p_numINNUMBER)ISBEGINdbms_output.put_line(rpad(p_label,40,'.')||p_num);END;PROCEDUREanalyze_space(f_segment_typeINVARCHAR2,f_segment_ownerINVARCHAR2,f_segment_nameINVARCHAR2,f_partition_nameINVARCHAR2)ISBEGINdbms_space.unused_space(segment_owner=>f_segment_owner,segment_name=>f_segment_name,segment_type=>f_segment_type,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused_bytes=>l_unused_bytes,LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,LAST_USED_BLOCK=>l_LAST_USED_BLOCK,partition_name=>f_partition_name);IFv_space='MANUAL'OR(v_space<>'auto'ANDv_space<>'AUTO')THENdbms_space.free_blocks(segment_owner=>f_segment_owner,segment_name=>f_segment_name,segment_type=>f_segment_type,freelist_group_id=>0,free_blks=>l_free_blks,partition_name=>f_partition_name);print('FreeBlocks',l_free_blks);ENDIF;IF(f_partition_nameISNULL)THENdbms_output.put_line('--'||rpad(f_segment_owner||'.'||f_segment_name,45,'-'));ELSEdbms_output.put_line('--'||rpad(f_segment_owner||'.'||f_segment_name||'.'||f_partition_name,45,'-'));ENDIF;print('TotalBlocks',l_total_blocks);print('TotalBytes',l_total_bytes);print('UnusedBlocks',l_unused_blocks);print('UnusedBytes',l_unused_bytes);print('LastUsedExtFileId',l_LastUsedExtFileId);print('LastUsedExtBlockId',l_LastUsedExtBlockId);print('LastUsedBlock',l_LAST_USED_BLOCK);/*IFthesegmentisanalyzed*/IFv_analyzed='Y'THENdbms_space.space_usage(segment_owner=>f_segment_owner,segment_name=>f_segment_name,segment_type=>f_segment_type,unformatted_blocks=>l_unformatted_blocks,unformatted_bytes=>l_unformatted_bytes,fs1_blocks=>l_fs1_blocks,fs1_bytes=>l_fs1_bytes,fs2_blocks=>l_fs2_blocks,fs2_bytes=>l_fs2_bytes,fs3_blocks=>l_fs3_blocks,fs3_bytes=>l_fs3_bytes,fs4_blocks=>l_fs4_blocks,fs4_bytes=>l_fs4_bytes,full_blocks=>l_full_blocks,full_bytes=>l_full_bytes,partition_name=>f_partition_name);dbms_output.put_line('Thesegmentisanalyzed.');print('0%--25%freespaceblocks',l_fs1_blocks);print('0%--25%freespacebytes',l_fs1_bytes);print('25%--50%freespaceblocks',l_fs2_blocks);print('25%--50%freespacebytes',l_fs2_bytes);print('50%--75%freespaceblocks',l_fs3_blocks);print('50%--75%freespacebytes',l_fs3_bytes);print('75%--100%freespaceblocks',l_fs4_blocks);print('75%--100%freespacebytes',l_fs4_bytes);print('UnusedBlocks',l_unformatted_blocks);print('UnusedBytes',l_unformatted_bytes);print('TotalBlocks',l_full_blocks);print('Totalbytes',l_full_bytes);dbms_output.put_line(rpad('',48,'-'));ENDIF;END;BEGINp_segment_name:=upper(v_segment_name);p_segment_owner:=upper(v_segment_owner);p_segment_type:=upper(v_segment_type);p_partition_name:=upper(v_partition_name);IF(v_segment_type='i'ORv_segment_type='I')THENp_segment_type:='INDEX';ENDIF;IF(v_segment_type='t'ORv_segment_type='T')THENp_segment_type:='TABLE';ENDIF;IF(v_segment_type='c'ORv_segment_type='C')THENp_segment_type:='CLUSTER';ENDIF;SELECTpartitionedINTOp_partitionedFROM(SELECTpartitionedFROMall_tablesWHEREowner=p_segment_ownerANDtable_name=p_segment_nameUNIONSELECTpartitionedFROMall_indexesWHEREowner=p_segment_ownerANDindex_name=p_segment_name);IF(p_segment_type='TABLE'ANDp_partitioned='YES')THENp_segment_type:='TABLEPARTITION';FORtIN(SELECTpartition_nameFROMall_tab_partitionsWHEREtable_owner=p_segment_ownerANDtable_name=p_segment_nameORDERBYto_number(regexp_substr(partition_name,'[0-9]*[0-9]',1)))loopanalyze_space(p_segment_type,p_segment_owner,p_segment_name,t.partition_name);ENDloop;ELSIF(p_segment_type='INDEX'ANDp_partitioned='YES')THENp_segment_type:='INDEXPARTITION';FORiIN(SELECTpartition_nameFROMall_tab_partitionsWHEREtable_owner=p_segment_ownerANDtable_name=p_segment_nameORDERBYto_number(regexp_substr(partition_name,'[0-9]*[0-9]',1)))loopanalyze_space(p_segment_type,p_segment_owner,p_segment_name,i.partition_name);ENDloop;ELSEanalyze_space(p_segment_type,p_segment_owner,p_segment_name,p_partition_name);ENDIF;EXCEPTIONWHENothersTHENdbms_output.put_line('Usage:');dbms_output.put_line('-execshow_space(''table'',''owner'',''table_name'');');dbms_output.put_line('-execshow_space(''tablepartition'',''owner'',''table_name'',''partition_name'');');dbms_output.put_line('-execshow_space(''index'',''owner'',''index_name'');');dbms_output.put_line('-execshow_space(''indexpartition'',''owner'',''index_name'',''partition_name'');');END;/