Remember it's an estimate. It's not going to be 100% accurate.
Also, although bytes-(avg_row_len*num_rows) shows 75,529,120 "wasted" space, in practice you're not going to save that much. Continuing the example above:
alter table t enable row movement;
alter table t shrink space;
select bytes from user_segments
where segment_name = 'T';
BYTES
59572224
So the saving was only 66,256,896.
Averages also hide a lot of detail.
Let's rebuild the table, with all the string columns set to null. Then update every other one to be 2,000 characters long:
drop table t purge;
create table t as
select level x, cast(null as varchar2(2000)) y from dual
connect by level <= 100000;
update t
set y = lpad('x', 2000, 'x')
where mod(x, 2) = 0;
commit;
exec dbms_stats.gather_table_stats(user, 't');
select avg_row_len * num_rows
from user_tables
where table_name = 'T';
AVG_ROW_LEN*NUM_ROWS
100600000
select bytes from user_segments
where segment_name = 'T';
BYTES
142606336 So we could save (142,606,336 - 100,600,000) = 42,006,336 bytes, right?
Let's try:
alter table t enable row movement;
alter table t shrink space;
select bytes from user_segments
where segment_name = 'T';
BYTES
142606336
WTF?! No change?!
Why's that?
Let's run the segment advisor instead:
declare
id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='T2';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'CHRIS',
attr2 => 'T',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
select af.message , af.more_info
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and af.task_name = 'T2';
MESSAGE MORE_INFO
The object has chained rows that can be removed by re-org. 49 percent chained rows can be removed by re-org.
The free space in the object is less than 10MB. Allocated Space:142606336: Used Space:137898749: Reclaimable Space :4707587:
Ahhh. There are chained rows. So we can't reclaim that space with a move/shrink anyway...
If you want to use avg_row_len*num_rows, go ahead. Just don't be surprised if your savings are nowhere near the figures this gives you.
Personally I'd stick with the segment advisor.