查看表的碎片及整理碎片

SET SERVEROUTPUTON
SET VERIFY OFF

 

declare

   v_fs1_bytes number;

   v_fs2_bytes number;

   v_fs3_bytes number;

   v_fs4_bytes number;

   v_fs1_blocks number;

   v_fs2_blocks number;

   v_fs3_blocks number;

   v_fs4_blocks number;

   v_full_bytes number;

   v_full_blocks number;

   v_unformatted_bytes number;

   v_unformatted_blocks number;

begin

   dbms_space.space_usage(

      segment_owner      => 'SCOTT',

      segment_name       => 'EMP',

      segment_type       => 'TABLE',

      fs1_bytes          => v_fs1_bytes,

      fs1_blocks         => v_fs1_blocks,

      fs2_bytes          => v_fs2_bytes,

      fs2_blocks         => v_fs2_blocks,

      fs3_bytes          => v_fs3_bytes,

      fs3_blocks         => v_fs3_blocks,

      fs4_bytes          => v_fs4_bytes,

      fs4_blocks         => v_fs4_blocks,

      full_bytes         => v_full_bytes,

      full_blocks        => v_full_blocks,

      unformatted_blocks => v_unformatted_blocks,

      unformatted_bytes  => v_unformatted_bytes

   );

   dbms_output.put_line('Number of blockshaving at least 0 to 25% free space = '||v_fs1_blocks);

       dbms_output.put_line('Numberof bytes having at least 0 to 25% free space = '||v_fs1_bytes);

   dbms_output.put_line('Number of blockshaving at least 25 to 50% free space = '||v_fs2_blocks);

       dbms_output.put_line('Numberof bytes having at least 25 to 50% free space = '||v_fs2_bytes);

   dbms_output.put_line('Number of blockshaving at least 50 to 75% free space = '||v_fs3_blocks);

       dbms_output.put_line('Numberof bytes having at least 50 to 75% free space = '||v_fs3_bytes);

   dbms_output.put_line('Number of bytes havingat least 75 to 100% free space = '||v_fs4_blocks);

       dbms_output.put_line('Numberof bytes having at least 75 to 100% free space = '||v_fs4_bytes);

   dbms_output.put_line('Total number of blocksfull in the segment = '||v_full_blocks);

       dbms_output.put_line('Numberof bytes allocated to the segment = '||v_full_bytes);

end;

两种整理碎片的方法:

1.alter table 表名 move

这种方法会使表相关的索引失效，之后还要执行alter index index_name rebuild online; 最后重新编译数据库所有失效的对象。

2.altertable 表名 shrink space cascade

执行这个命令前需开启表的行移动altertable table_name enable row movement ;

另外还可以把表导出后truncate，然后再把表导入，不过这种方法会使得业务暂时不可用。