Script to find over-allocated tables in Oracle

Over-allocated space is a very common problem in many applications where explosive data growth occurs, and is not just an issue for users of SolarWinds Database Performance Analyzer. This is just an example of how to do this in a DPA environment, but the same scripts can be used in any database.

DPA detail tables can grow very 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 will summarize the detailed data into summary tables for long term storage and purge 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.

Below is a script that can be used to detect this problem and provides a ranking of the tables with the most data bloat, i.e. the tables that have the most unused space.

How to shrink a table

To shrink a table and reclaim this wasted space, you can use the following commands:

alter table <table_name> enable row movement;
alter table <table_name> shrink space; 
alter table <table_name> disable row movement;

This is just one way to shrink the table, but is probably the easiest. Other methods include:

• create table as select (CTAS). However, you woulud also have to manually rebuild the indexes and then rename objects at the end
• Online regorganization with DBMS_REDEFINITION package.
• alter table move
• export/import and data pump (expdp/impdp)

Script to find over-allocated tables

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
/