Running the Segment Advisor Manually

--回收表空间
alter table test enable row movement; --开启行迁移功能。
alter table test shrink space compact;--(可以在压缩期间进行DML操作和查询) ,收缩表，不会降低hwm
alter table test shrink space; --( 调整HWM时将阻塞DML操作),收缩表，并且降低hwm
alter table test_idx shrink space cascade;--收缩表并降低hwm，并且回收相应的索引。

alter   shrink space parallel ;
    or
alter   shrink space compact parallel ; 

建立任务：
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            => 'SCOTT',
    attr2            => 'T1',
    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; 
/


删除任务：
DECLARE
BEGIN
dbms_advisor.delete_task (
task_name => 'Manual_Employees'
);
END;
/


查看任务是否完成：
select task_name, status from dba_advisor_tasks
   where advisor_name = 'Segment Advisor';


查看结果：
SET LINESIZE 500;
COL SEGNAME FORMAT A10;
COL TASK_NAME FORMAT A20;
COL TYPE FORMAT A10;
COL MESSAGE FORMAT A30;
col PARTITION format a10;
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id and ao.type='TABLE' and ao.owner NOT IN ('SYS','SYSTEM');
  




select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')); 第一个参数true表示运行历次运行结果，false表示最近一次的结果  第二个参数true表示返回手工运行段顾问的结果，false表示返回自动运行段顾问的结果  第三个参数true表示仅显示分析结果，false表示显示分析结果和分析建议 --查看昨天到今天运行的调优顾问（11G）
select
'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || 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 || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name; 

exec dbms_space.auto_space_advisor_job_proc --查看表是否有回收空间
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('FALSE', 'FALSE', 'FALSE'));

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

转载于:http://blog.itpub.net/20747382/viewspace-2131791/