oracle碎片化分析：发现频繁删除的业务表

create table suipian(owner  varchar2(100),segment_name varchar2(100),segment_type varchar2(100),unformatted_blocks number,
    fs1 number,fs2 number,fs3 number,fs4 number,full number,bz varchar2(100));

 

 

CREATE OR REPLACE PROCEDURE show_spaceall AS
  l_free_blks          NUMBER;
  l_total_blocks       NUMBER;
  l_total_bytes        NUMBER;
  l_unused_blocks      NUMBER;
  l_unused_bytes       NUMBER;
  l_LastUsedExtFileId  NUMBER;
  l_LastUsedExtBlockId NUMBER;
  l_LAST_USED_BLOCK    NUMBER;
  l_segment_space_mgmt VARCHAR2(255);
  l_unformatted_blocks NUMBER;
  l_unformatted_bytes  NUMBER;
  l_fs1_blocks         NUMBER;
  l_fs1_bytes          NUMBER;
  l_fs2_blocks         NUMBER;
  l_fs2_bytes          NUMBER;
  l_fs3_blocks         NUMBER;
  l_fs3_bytes          NUMBER;
  l_fs4_blocks         NUMBER;
  l_fs4_bytes          NUMBER;
  l_full_blocks        NUMBER;
  l_full_bytes         NUMBER;
  -- inline procedure to print out numbers nicely formatted
  -- with a simple label
  l_bz  VARCHAR2(255);
BEGIN

  for r in (select seg.*
              from dba_segments seg, dba_tablespaces ts
             where seg.owner = 'LZJBSI'
               and seg.tablespace_name = ts.tablespace_name
               and seg.segment_type = 'TABLE'
               and segment_space_management = 'AUTO'
               and seg.segment_name not like '%$%'
               and seg.extents > 100) loop
  
    dbms_space.space_usage(r.owner,
                           r.segment_name,
                           r.segment_type,
                           l_unformatted_blocks,
                           l_unformatted_bytes,
                           l_fs1_blocks,
                           l_fs1_bytes,
                           l_fs2_blocks,
                           l_fs2_bytes,
                           l_fs3_blocks,
                           l_fs3_bytes,
                           l_fs4_blocks,
                           l_fs4_bytes,
                           l_full_blocks,
                           l_full_bytes,
                           null);
    p('Unformatted Blocks ', l_unformatted_blocks);
    p('FS1 Blocks (0-25) ', l_fs1_blocks);
    p('FS2 Blocks (25-50) ', l_fs2_blocks);
    p('FS3 Blocks (50-75) ', l_fs3_blocks);
    p('FS4 Blocks (75-100)', l_fs4_blocks);
    p('Full Blocks ', l_full_blocks);
    
    
    if l_fs1_blocks+l_fs2_blocks+l_fs3_blocks+l_fs4_blocks>l_full_blocks then
     l_bz:='严重100%';
    elsif l_fs1_blocks+l_fs2_blocks+l_fs3_blocks+l_fs4_blocks>l_full_blocks/2 then
     l_bz:='严重50%';
    elsif l_fs1_blocks+l_fs2_blocks+l_fs3_blocks+l_fs4_blocks>l_full_blocks/5 then
     l_bz:='普通20%';
    elsif l_fs1_blocks+l_fs2_blocks+l_fs3_blocks+l_fs4_blocks>0 then
     l_bz:='普通';
     else
       l_bz:='无';
     end if;
     
    
    insert into suipian(owner,segment_name,segment_type,unformatted_blocks,
    fs1,fs2,fs3,fs4,full,bz)
    values(r.owner,r.segment_name,r.segment_type,l_unformatted_blocks,
    l_fs1_blocks,l_fs2_blocks,l_fs3_blocks,l_fs4_blocks,l_full_blocks,l_bz);
    
  end loop;

END;

 

 

本文地址：https://blog.csdn.net/viviliving/article/details/107631615