Segment advisor per schema

shrink: Segment advisor per schema.sql

DECLARE
  l_object_id  NUMBER;
BEGIN
 -- Create a segment advisor task for the PAPERLESS tablespace.
DBMS_ADVISOR.create_task (
advisor_name      => 'Segment Advisor',
task_name         => 'PAPERLESS_SEGMENT_ADVISOR',
task_desc         => 'Segment Advisor For PAPERLESS');

DBMS_ADVISOR.create_object (
task_name   => 'PAPERLESS_SEGMENT_ADVISOR',
object_type => 'TABLESPACE',
attr1       => 'PAPERLESS', 
attr2       => NULL, 
attr3       => NULL, 
attr4       => 'null',
attr5       => NULL,
object_id   => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => 'PAPERLESS_SEGMENT_ADVISOR',
parameter => 'RECOMMEND_ALL',
value     => 'TRUE');

DBMS_ADVISOR.execute_task(task_name => 'PAPERLESS_SEGMENT_ADVISOR');
END;
/ 

Mon Aug 22 12:31:44 CDT 2016

-- 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 IN ('PAPERLESS_SEGMENT_ADVISOR')
ORDER BY f.task_name, f.impact DESC;