Oracle Automatic Segment Advisor - Read Advisor findings

Oracle Automatic Segment Advisor runs during maintenance window and collects information regarding various facets of your database, namely SQL optimization, table segment fragmentation, I/O wait reduction methods, ...

You can use a combination of DBA_ADVISOR_FINDINGS, DBA_ADVISOR_OBJECTS AND DBA_ADVISOR_LOG to read those findings.

The underlying query gives you the findings related to tables.

SELECT TO_CHAR(log.execution_start, 'YYYY-MM-DD') AS "ADVISOR DATE",
  af.task_name,
  ao.owner,
  ao.attr2 segname,
  ao.attr3 partition,
  ao.type,
  af.message
FROM dba_advisor_findings af,
  dba_advisor_objects ao,
  dba_advisor_log log
WHERE ao.task_id        = af.task_id
AND ao.task_id          = log.task_id
AND ao.object_id        = af.object_id
AND ao.type             = 'TABLE'
AND log.execution_start > SYSDATE-1
ORDER BY log.execution_start DESC, ao.attr2;