1、查看segment advisor

方法一：

select * fromtable(dbms_space.asa_recommendations(‘TRUE‘,‘FALSE‘,‘FALSE‘));

dbms_space.asa_recommentdations的三个参数：

all_runs      TURE返回历次运行的结果，FALSE则返回最近一次运行的结果

show_manual   TURE返回手工执行segment advisor的执行结果，FALSE返回自动执行的segment advisor的执行结果

show_finding  TURE仅显示结果不显示建议，FALSE显示结果和建议

方法二：

select f.task_name,

       execution_start,

       o.attr2,

       o.type,

       o.attr3,

       f.message,

       f.more_info

  from dba_advisor_executions e,

       dba_advisor_findings   f,

       dba_advisor_objects    o

 where o.task_id = f.task_id

   and o.object_id = f.object_id

   and f.task_id = e.task_id

   and e.advisor_name = ‘Segment Advisor‘

 order by f.task_name;

segment advisor会自动的定期执行，查看其执行情况的视图是dba_auto_segadv_summary

查看ora advisor的视图

dba_advisor_executions

dba_advisor_findings

dba_advisor_objects

2、手工生成segment advisor

查看advisor name

select * fromdba_advisor_definitions;

ADVISOR_IDADVISOR_NAME                     PROPERTY

---------------------------------------- ----------

         1 ADDM                                    1

         2 SQL Access Advisor                    271

         3 Undo Advisor                            1

         4 SQL Tuning Advisor                    935

         5 Segment Advisor                        67

         6 SQL Workload Manager                    0

         7 Tune MView                             31

         8 SQL Performance Analyzer              935

         9 SQL Repair Advisor                    679

        10 Compression Advisor                     3

生成segmentadvisor

DECLARE

  my_task_id  number;

  obj_id      number;

  my_task_name varchar2(100);

  my_task_desc varchar2(500);

BEGIN

  my_task_name := ‘BIG_TABLE Advice‘;

  my_task_desc := ‘Manual Segment Advisor Run‘;

  -- Step 1创建一个任务

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

                           task_id      => my_task_id,

                           task_name    => my_task_name,

                           task_desc    => my_task_desc);

  -- Step 2为这个任务分配一个对象

  dbms_advisor.create_object(task_name   => my_task_name,

                             object_type =>‘TABLE‘,

                             attr1       => ‘SYSTEM‘,

                             attr2       => ‘AL_APPLY‘,

                             attr3       => NULL,

                             attr4       => NULL,

                             attr5       => NULL,

                             object_id   => obj_id);

  -- Step 3设置任务参数

  dbms_advisor.set_task_parameter(task_name=> my_task_name,

                                  parameter=> ‘recommend_all‘,

                                  value     => ‘TRUE‘);

  -- Step 4执行这个任务

  dbms_advisor.execute_task(my_task_name);

END;

/

说明：

Table 14-2 DBMS_ADVISOR packageprocedures relevant to the Segment Advisor

Table 14-3 Input forDBMS_ADVISOR.CREATE_OBJECT

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;(只整理碎片，不调整高水位线)--如果整理碎片和降低高水位话费时间会很长，用于分开操作