Automatic Segment Advisor is one of AUTOTASK in the Oracle database, which can discover segment space issues, mostly, they have significant unused space.
AUTOTASK are enabled by default, however, you can disable AUTOTASK if the overall performance of the database have been impacted.
To check what findings of Automatic Segment Advisor has, we mainly query DBA_ADVISOR_FINDINGS to retrieve the latest findings.
In this case, we retrieve segment findings within 3 days.
SQL> set pagesize 1000 linesize 120;
SQL> column execution_start format a20;
SQL> column type format a10;
SQL> column message format a50;
SQL> select a.execution_start, c.type, b.message from dba_advisor_executions a, dba_advisor_findings b, dba_advisor_objects c where a.task_id = b.task_id and a.task_id = c.task_id and b.object_id = c.object_id and a.advisor_name = 'Segment Advisor' and a.execution_start > sysdate - 3 and b.message is not null order by a.task_id, b.object_id;
Let's see the result.
EXECUTION_START TYPE MESSAGE
-------------------- ---------- --------------------------------------------------
2024-09-29 22:08:45 TABLE Compress object ERPAPP.HI_MR_SHUTTLE2, estimated s
avings is 10485760 bytes.
2024-09-29 22:08:45 TABLE Compress object ERPAPP.HI_PATH_KURB_D, estimated s
avings is 10485760 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HM_KP, estimated savings is
149321416704 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HI_HISTORY_S, estimated sav
ings is 62094573568 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HM_READ_LOGR, estimated sav
ings is 27854372864 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HM_PATH, estimated savings
is 42229301248 bytes.
2024-09-30 22:04:47 TABLE Compress object REMS.HI_PAYMT_SNAPSHOT_FR, estimat
ed savings is 68157440 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HI_HISTORY_S_MISTI, estimat
ed savings is 45088768 bytes.
2024-09-30 22:04:47 TABLE Compress object ERPAPP.HI_CO_SWIFT_ON, estimated s
avings is 10485760 bytes.
9 rows selected.
For table object, you have several options to reclaim unused space.