Hi,
The shrink table doesn't defragments the partition tables.
executed : alter table test_table_1 shrink space
and checked with below query wasted space before and after but the values are identical.
select
table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "WASTED SPACE (kb)"
from
dba_tables
where
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;
PLEASE let me know how do I ifx this issue?!
Thanks,
Sesub
So two questions:
- Have you re-gathered stats on the table after shrinking it?
- Are you sure there was wasted space you could reclaim?
Because it all looks fine to me:
create table t (
x not null, y not null
) partition by range (x) (
partition p0 values less than (500),
partition p1 values less than (1000)
) enable row movement as
select rownum, lpad('x', 1000, 'x') from dual
connect by level < 1000;
exec dbms_stats.gather_table_stats(user, 't');
select table_name,
round( ( blocks * 8 ),2 ) "size (kb)",
round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;
TABLE_NAME size (kb) actual_data (kb) WASTED SPACE (kb)
T 1264 980.46 283.54
delete t where mod(x, 2) = 0;
commit;
exec dbms_stats.gather_table_stats(user, 't');
select table_name,
round( ( blocks * 8 ),2 ) "size (kb)",
round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;
TABLE_NAME size (kb) actual_data (kb) WASTED SPACE (kb)
T 1264 490.72 773.28
alter table t shrink space;
select table_name,
round( ( blocks * 8 ),2 ) "size (kb)",
round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;
TABLE_NAME size (kb) actual_data (kb) WASTED SPACE (kb)
T 1264 490.72 773.28
exec dbms_stats.gather_table_stats(user, 't');
select table_name,
round( ( blocks * 8 ),2 ) "size (kb)",
round( ( num_rows * avg_row_len / 1024 ),2 ) "actual_data (kb)",
( round( ( blocks * 8 ),2 ) - round( ( num_rows * avg_row_len / 1024 ),2 ) ) "WASTED SPACE (kb)"
from user_tables
where table_name = 'T'
order by 4 desc;
TABLE_NAME size (kb) actual_data (kb) WASTED SPACE (kb)
T 592 490.72 101.28 Notice the wasted space only drops after regathering stats.
So what exactly are you doing? Show us your complete test case! (create table + inserts + shrink)