检测表是否需要shrink,Segment advisor

  exec  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; 
   select a.owner || '.' || a.table_name "Table",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,
   round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4) "Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"
        from dba_tables a left join dba_tab_modifications b
      on  a.owner=b.table_owner and a.table_name=b.table_name  inner join dba_segments c on a.owner=c.owner and a.table_name=c.segment_name
  where a.last_analyzed is not null and a.partitioned='NO' and a.owner not in
  ('SYS', 'SYSTEM', 'SYSMAN', 'DMSYS', 'OLAPSYS','XDB', 'EXFSYS', 'CTXSYS', 'WMSYS', 'DBSNMP', 'ORDSYS', 'OUTLN', 'TSMSYS', 'MDSYS')
  and (a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0)))/c.bytes<0.5;

上面的脚本(注意，该脚本只能检测被分析过的表，另外我也没有考虑分区表)可以初步判断数据库中所有表是否需要shrink，得到关于表的概要信息之后，我们可以利用10g中的Segment advisor来判断该表是否需要shrink，可以用oem来调用segment advisor，不过很多情况下oem并没有打开，所以这里介绍如何手动执行Segment advisor。

Segment advisor 例子:

SQL> show user

User is "robinson"

SQL> create table test as select * from dba_objects;

 

Table created

 

SQL> insert into test select * from dba_objects;

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL> /

 

49792 rows inserted

 

SQL>  select owner,segment_name,segment_type,bytes/1024/1024 "Size(Mb)" from dba_segments where segment_name='TEST';

 

OWNER      SEGMENT_NA SEGMENT_TYPE         Size(Mb)

---------- ---------- ------------------ ----------

ROBINSON   TEST       TABLE                      60

 

SQL> commit;

 

Commit complete

SQL> select count(*) from test;

 

  COUNT(*)

----------

    547712

 

SQL> delete from test where rownum<547712/2;

 

273855 rows deleted

 

SQL> commit;

 

Commit complete

SQL> variable task_id    number;

SQL> begin

  2  declare

  3  object_id      number;

  4  name           varchar2(100);

  5  task_desc      varchar2(100);

  6  begin

  7  name := 'test';

  8  task_desc := 'Segment Advisor TEST';

  9  dbms_advisor.create_task(advisor_name => 'Segment Advisor',

 10                           task_id => :task_id,

 11                           task_name => name,

 12                           task_desc => task_desc

 13                           );

 14  dbms_advisor.create_object(task_name => name,

 15                             object_type => 'TABLE',

 16                             attr1 => 'ROBINSON',

 17                             attr2 => 'TEST',

 18                             attr3 => NULL,

 19                             attr4 => NULL,

 20                             attr5 => NULL,

 21                             object_id =>object_id

 22                             );

 23   dbms_advisor.set_task_parameter(task_name => name,

 24                                   parameter => 'recommend_all',

 25                                   value     => 'TRUE'

 26                                   );

 27   dbms_advisor.execute_task(task_name => name);

 28   end;

 29   end;

 30  /

 

PL/SQL procedure successfully completed

task_id

---------

560

SQL> col task_name format a8

SQL> col segname format a8

SQL> col partition  format a8

SQL> col type  format a8

SQL> col message format a100

SQL>  select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message

  2    from dba_advisor_findings af, dba_advisor_objects ao

  3   where ao.task_id = af.task_id

  4   and ao.object_id = af.object_id

  5   and ao.owner = 'ROBINSON';

 

TASK_NAM SEGNAME  PARTITIO TYPE     MESSAGE

-------- -------- -------- -------- ----------------------------------------------------------------------------------------------------

test     TEST              TABLE    Enable row movement of the table ROBINSON.TEST and perform shrink, estimated savings is 28451785 byt

                                    es.

SQL> select 28451785/1024/1024 from dual;

 

28451785/1024/1024

------------------

  27.1337366104126

从Segment advisor里面得到如果对表进行shrink 可以节约27M的空间，根据例子的操作步骤也可以得出这个结论，因为我删除了一般的数据

，在未删除之前表test占用了60M。由此可见Segment advisor的结果还是比较准确的。

查看task信息:

select * from dba_advisor_tasks where advisor_name!='ADDM';

删除task:

 

SQL>  exec dbms_advisor.delete_task(task_name => 'test');

 

PL/SQL procedure successfully completed