Over-allocated space is a common problem in many applications where explosive data growth occurs and is not just a DPA issue.
DPA detail tables can grow quickly when a large performance anomaly occurs. When an anomaly occurs, more data is typically collected by DPA than normal causing the data bloat. By default after 30 days, DPA summarizes the detailed data into summary tables for long term storage and purges the bloated detailed data. However, purging the data originating from the performance anomoly does not shrink the underlying tables and you can end up with wasted space.
At the bottom of this article is a script that can be used to detect this problem and provides a ranking of the tables with the most data bloat. In other words, the tables that have the most unused space. To shrink a table and reclaim this wasted space, you can use the following commands:
alter table enable row movement; alter table shrink space; alter table disable row movement;
This is just one way to shrink the table, but is probably the easiest. Other methods include:
drop table ignite_space / create table ignite_space as select owner, table_name, 0 space_used, 0 space_alloc, 0 chain_pct from dba_tables where 1=2 / declare nSpace_Used NUMBER; nSpace_Alloc NUMBER; nChain_Pct NUMBER; begin for t in (select user owner, segment_name table_name from user_segments where segment_type='TABLE') loop begin dbms_space.object_space_usage (t.owner, t.table_name, 'TABLE', NULL, nSpace_Used, nSpace_Alloc, nChain_Pct); insert into ignite_space (owner, table_name, space_used, space_alloc, chain_pct) values (t.owner, t.table_name, nSpace_Used, nSpace_Alloc, nChain_Pct); commit; exception when others then insert into ignite_space (owner, table_name, space_used, space_alloc, chain_pct) values (t.owner, t.table_name, -1, -1, -1); commit; end; end loop; end; / -- show top 20 space savers if shrunk select * from ( select table_name, round((space_alloc-space_used)/1024/1024,2) mb_save, round(space_alloc/1024/1024,2) mb_alloc, round(space_used/1024/1024,2) mb_used, round(chain_pct,2) chain_pct from ignite_space order by 2 desc) where rownum<=20 /