segment advisor:no segment found

在em中使用segment advisor的时执行完毕查看结果的时侯收到no segment found，原因是当segment 里面的free space小于10m的时侯oracle可能认为这个segment 里的free space太小了，可能没有必要处理，所以也不会给出任何建议，最后就是这个提示。[@more@]

SQL> select type,message,more_info from dba_advisor_findings
2 where task_name in ('SEGMENTADV_4328829','SEGMENTADV_9662658','SEGMENTADV_9854361');

TYPE MESSAGE MORE_INFO
---------------------- ---------------------------------------- ----------------------------------------------------
PROBLEM The free space in the object is less tha Allocated Space:5242880: Used Space:5067168: Reclaim
n 10MB. able Space :175712:

PROBLEM The free space in the object is less tha Allocated Space:41943040: Used Space:40156119: Recla
n 10MB. imable Space :1786921:

INFORMATION Perform shrink, estimated savings is 123 Allocated Space:41943040: Used Space:29633599: Recla
09441 bytes. imable Space :12309441:

SQL>

任务执行之后出现no segment found之后，dba_advisor_findings里面的type是PROBLEM，oracle认为这个任务是有问题的，否则的话type是INFORMATION，需要shrink的话会给出shrink建议。不习惯使用em的话也可以通过api来执行：

segment advisor Segment Advisor是10g中新增的一个工具，可以用来估算表的空间利用率，并给出相应的建议，确定是否需要进行shrink。以下为测试过程，下面的内容来着连接：

http://hi.baidu.com/whxaszxcv/item/58cc5610e4d32fa3ffded5c7

防止连接失效，把内容拷贝了过来：

1. 表test大小为80M，初始时数据比较紧凑，对其执行批量delete操作

CREATE TABLE TEST AS SELECT * FROM DBA_SEGMENTS;

INSERT INTO TEST SELECT * FROM TEST;

INSERT INTO TEST SELECT * FROM TEST;

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------- ------------------ ---------------
TEST TABLE 80

SQL> select count(*) from test;

COUNT(*)
----------
738624

SQL> delete from test where rownum<=400000;

400000 rows deleted.

SQL> commit;

Commit complete.

2.执行Segment Advisor，对表的存储空间进行检查

SQL> variable id number;
SQL> begin

declare

object_id number;

name varchar2(100);

task_desc varchar2(100);

begin

name := 'ASS';-----(任务名字)

task_desc := 'Segment Advisor A';

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

task_id => :task_id,

task_name => name,

task_desc => task_desc

);

dbms_advisor.create_object(task_name => name,

object_type => 'TABLE',

attr1 => 'SYS',--------系统帐号

attr2 => 'A',------表的名字

attr3 => 'SS',------分区名字（如果没有，就用NULL）

attr4 => NULL,

attr5 => NULL,

object_id =>object_id

);

dbms_advisor.set_task_parameter(task_name => name,

parameter => 'recommend_all',

value => 'TRUE'

);

dbms_advisor.execute_task(task_name => name);

end;

end;

PL/SQL procedure successfully completed.

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 = 'SYS';

TASK_NAME SEGNAME PARTITION TYPE MESSAGE
-------------------- -------------------- ---------- ---------- -------------------------
TEST TEST TABLE Enable row movement of th
e table SYS.TEST and perf
orm shrink, estimated sav
ings is 43132568 bytes.

从可以看出，Segment Advisor给出的建议是对表做shrink，并且估算出可以节省的空间约为43132568 bytes。

3. 执行shrink操作

SQL> select table_name,row_movement from user_tables where table_name='TEST';

TABLE_NAME ROW_MOVE
------------------------------ --------
TEST DISABLED

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------ ---------------
TEST TABLE 34

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1060139/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1060139/