DECLARE
  l_object_id  NUMBER;
BEGIN
  -- Create a segment advisor task for the SCOTT.EMP table.
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => 'CATSDB____1_SEGMENT_ADVISOR',
    task_desc         => 'Segment Advisor For CATSDB');
DBMS_ADVISOR.create_object (
    task_name   => 'CATSDB____1_SEGMENT_ADVISOR',
    object_type => 'INDEX',
    attr1       => 'SAPR3', 
    attr2       => 'CATSDB____1', 
    attr3       => NULL, 
    attr4       => 'null',
    attr5       => NULL,
    object_id   => l_object_id);
DBMS_ADVISOR.set_task_parameter (
    task_name => 'CATSDB____1_SEGMENT_ADVISOR',
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => 'CATSDB____1_SEGMENT_ADVISOR');
end;
/ 

-- Display the findings.
SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
       f.impact,
       o.type AS object_type,
       o.attr1 AS schema,
       o.attr2 AS object_name,
       f.message,
       f.more_info
FROM   dba_advisor_findings f
       JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE  f.task_name ='CATSDB____1_SEGMENT_ADVISOR'
ORDER BY f.task_name, f.impact DESC;