Table 14-4 Input forDBMS_ADVISOR.SET_TASK_PARAMETER

Example:

variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';

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 => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
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;
end;
/

查看segmentadvisor结果

SELECT

'Segment Advice --------------------------'||chr(10) ||

'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||

'SEGMENT_OWNER : ' || segment_owner || chr(10) ||

'SEGMENT_NAME : ' || segment_name || chr(10) ||

'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||

'RECLAIMABLE_SPACE: ' || reclaimable_space ||chr(10) ||

'RECOMMENDATIONS : ' || recommendations || chr(10) ||

'SOLUTION 1 : ' || c1 || chr(10) ||

'SOLUTION 2 : ' || c2 || chr(10) ||

'SOLUTION 3 : ' || c3 Advice

FROM

TABLE(dbms_space.asa_recommendations('TRUE','TRUE', 'FALSE'));

或者：

SELECT

'Task Name : ' || f.task_name || chr(10) ||

'Segment Name : ' || o.attr2 || chr(10) ||

'Segment Type : ' || o.type || chr(10) ||

'Partition Name : ' || o.attr3 || chr(10) ||

'Message : ' || f.message || chr(10) ||

'More Info : ' || f.more_info TASK_ADVICE

FROMdba_advisor_findings f

,dba_advisor_objects o

WHERE o.task_id =f.task_id

AND o.object_id =f.object_id

AND f.task_name like'BIG_TABLE Advice'

ORDER BYf.task_name;

删除segmentadvisor

execdbms_advisor.delete_task('BIG_TABLE Advice');

释放未使用的表的空间

alter tabletablename enable rowmovement;

alter tabletablename shrink spcace;

alter table tablename shrink spcace cascade;(级联收缩索引空间)

alter table tablename shrink spcace compact;(只整理碎片，不调整高水位线)--如果整理碎片和降低高水位话费时间会很长，用于分开操作