检测表是否需要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