分析表碎片的脚本

1.授予用户select any dictionary 的权限

 

grant select any dictionary to &owner;

 

 

2.创建tmp_frag表

 

drop table tmp_frag;

create  table tmp_frag ( 
  owner char(30), 
  name char(30), 
  hwm number, 
  blks_w_rows number, 
  avg_row_size number, 
  possible_bytes_per_block number, 
  no_frag_rows number, 
  no_extents number 
) ;

 

3.给tmp_frag 表创建索引

create unique index tfrag_u1 on tmp_frag (owner,name);

 

4.创建存储过程test_frag

create or replace procedure  test_frag(ownname varchar2)
is
 vblocks integer;
 vempty_blocks integer;
 valloc_blocks integer;
 vhwm integer;
 vcr integer;
 vsf integer;
begin
for c in (select table_name from dba_tables where owner=upper(ownname)) loop
    begin 

      select blocks val1, 
             empty_blocks val2 ,chain_cnt
      into
             vblocks,vempty_blocks,vcr
      from   dba_tables 
      where  owner = upper(ownname) and 
            table_name = c.table_name;           
      select blocks
      into  valloc_blocks
      from   dba_segments 
      where owner        = upper(ownname) and 
       segment_name = upper(c.table_name) ;
      vhwm:=valloc_blocks-vempty_blocks;
      select count(*) into vsf from dba_extents
      where owner        = upper(ownname) and 
       segment_name = upper(c.table_name) ;
      insert into tmp_frag values  
      (ownname,c.table_name,vhwm,vblocks,0,0,vcr,vsf);
     exception when no_data_found then dbms_output.put_line('error in:'||c.table_name||',code=');
     end;
      commit; 
  end loop;
end;

 

 

5分析某个用户下表碎片的情况

-- 分析前线truncate掉tmp_frag表的历史数据

SQL> Truncate table tmp_frag;

Table truncated

--执行存储过程，输入要分析的shecma的名称

 SQL> Exec test_frag(ownname=>'report');

  PL/SQL procedure successfully completed

 

SQL>

--查看表碎片情况
SQL>  select owner towner,
  2          name tname,
  3          no_extents exts,
  4          (hwm - blks_w_rows) / (hwm + 0.0001) omega1,
  5          no_frag_rows chains
  6     from tmp_frag
  7    order by 1, 2
  8  ;

 

 

TOWNER    TNAME                 EXTS     OMEGA1        CHAINS
--------- ---------------            ----------   ---------         ----------
report    ACCMAINVOUCHER           7   0.01897321          0
report    AXT                                   1    0.49999375         0

 

 

-- 此存储过程的摘自老白的《ORACLE RAC 日记》P 245, 本文简单记录测试的结果