OK, here's where I think the conflicting messages come into play. Segment advisor will look at *any* segment, but its going to give differing advice based on the tablespace management, eg
Segment space management auto
=============================
SQL> create table scott.demo_table as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4129500 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 594530643 bytes.
More info : Allocated Space:1342177280: Used Space:747646637: Reclaimable Space :594530643:
PL/SQL procedure successfully completed.
Segment space management manual
=============================
SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
2 @pr
==============================
TABLESPACE_NAME : NO_ASSM
BLOCK_SIZE : 8192
INITIAL_EXTENT : 1048576
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MAX_SIZE : 2147483645
PCT_INCREASE : 0
MIN_EXTLEN : 1048576
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : UNIFORM
PLUGGED_IN : NO
SEGMENT_SPACE_MANAGEMENT : MANUAL
DEF_TAB_COMPRESSION : DISABLED
RETENTION : NOT APPLY
BIGFILE : NO
PREDICATE_EVALUATION : HOST
ENCRYPTED : NO
COMPRESS_FOR :
DEF_INMEMORY : DISABLED
DEF_INMEMORY_PRIORITY :
DEF_INMEMORY_DISTRIBUTE :
DEF_INMEMORY_COMPRESSION :
DEF_INMEMORY_DUPLICATE :
SHARED : SHARED
DEF_INDEX_COMPRESSION : DISABLED
INDEX_COMPRESS_FOR :
DEF_CELLMEMORY :
DEF_INMEMORY_SERVICE :
DEF_INMEMORY_SERVICE_NAME :
LOST_WRITE_PROTECT : OFF
CHUNK_TABLESPACE : N
PL/SQL procedure successfully completed.
SQL>
SQL> create table scott.demo_table tablespace no_assm as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 100 ) ;
Table created.
SQL>
SQL> delete from scott.demo_table
2 where mod(object_id,2) = 0;
4129500 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
2 l_object_id number;
3 l_task_name varchar2(50) := 'TEST_TASK';
4 l_object_type varchar2(50) := upper('TABLE');
5 l_attr1 varchar2(50) := upper('SCOTT');
6 l_attr2 varchar2(50) := upper('DEMO_TABLE');
7 begin
8 begin DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
9
10 dbms_advisor.create_task (
11 advisor_name => 'Segment Advisor',
12 task_name => l_task_name);
13
14 dbms_advisor.create_object (
15 task_name => l_task_name,
16 object_type => l_object_type,
17 attr1 => l_attr1,
18 attr2 => l_attr2,
19 attr3 => NULL,
20 attr4 => 'null',
21 attr5 => NULL,
22 object_id => l_object_id);
23
24 dbms_advisor.set_task_parameter (
25 task_name => l_task_name,
26 parameter => 'RECOMMEND_ALL',
27 value => 'TRUE');
28
29 dbms_advisor.execute_task(task_name => l_task_name);
30
31
32 for cur_rec in (select f.impact,
33 o.type,
34 o.attr1,
35 o.attr2,
36 f.message,
37 f.more_info
38 from dba_advisor_findings f
39 join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
40 where f.task_name = l_task_name
41 order by f.impact desc)
42 loop
43 dbms_output.put_line('..');
44 dbms_output.put_line('Type : ' || cur_rec.type);
45 dbms_output.put_line('Attr1 : ' || cur_rec.attr1);
46 dbms_output.put_line('Attr2 : ' || cur_rec.attr2);
47 dbms_output.put_line('Message : ' || cur_rec.message);
48 dbms_output.put_line('More info : ' || cur_rec.more_info);
49 end loop;
50
51 dbms_advisor.delete_task(task_name => l_task_name);
52 end;
53 /
..
Type : TABLE
Attr1 : SCOTT
Attr2 : DEMO_TABLE
Message : Perform re-org on the object DEMO_TABLE, estimated savings is 564585313 bytes.
More info : Allocated Space:1307574272: Used Space:742988959: Reclaimable Space :564585313:
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
Shout out to Tim Hall - I borrowed one of his scripts