10G的Advisor以前没有太关注过,觉悟太晚了,这11G R2都出来了呵呵.

弄一个for tablespace的吧, 就是执行过一遍,太慢了就是,生产库慎行.

variable id number;
variable v_name char;
variable v_tbs char;
accept v_name CHAR prompt 'Please enter the task name';
accept v_tbs char prompt 'Please enter the tablespace name to be analyzed';
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
v_tbs varchar2(100);
begin
name := :v_name;
v_tbs := :v_tbs;

descr := 'Segment Advisor For Tablespace ';
dbms_advisor.create_task(advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);

dbms_advisor.create_object(task_name => name,
object_type => 'TABLESPACE',
attr1 => v_tbs,
attr2 => null,
attr3 => null,
attr4 => null,
attr5 => null,
object_id => obj_id);

dbms_advisor.set_task_parameter(task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
/

最后,用下面的看下结果吧. 命令都给出来了,还想咋着,Oracle想的真周到

select segment_owner,segment_name,segment_type,partition_name,allocated_space/1024/1024 "allocate(mb)",TRUNC(reclaimable_space/1024/1024,2) "reclaim(MB)",C3,C2,C1
from table (dbms_space.asa_recommendations())
ORDER BY "reclaim(MB)" DESC;