Submit a ticketCall us

WebinarUpcoming Webinar: Should I Move My Database to the Cloud?

So you’ve been running an on-premises SQL Server® for a while now. Maybe you’ve moved it from bare metal to a VM, and have seen some positive benefits. But, do you want to see more? If you said “YES!”, then this session is for you, as James Serra will review the many benefits that can be gained by moving your on-prem SQL Server to an Azure® VM (IaaS). He’ll also talk about the many hybrid approaches, so you can gradually move to the cloud. If you are interested in cost savings, additional features, ease of use, quick scaling, improved reliability, and ending the days of upgrading hardware, this is the session for you.

Register now.

Home > Success Center > Database Performance Analyzer (DPA) > DPA - Knowledgebase Articles > Over-allocated space for DPA repository tables in Oracle

Over-allocated space for DPA repository tables in Oracle

Created by Interspire Import, last modified by MindTouch on Jun 23, 2016

Views: 751 Votes: 0 Revisions: 11

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:

  • Create table as select (CTAS). However, you would 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
    nSpace_Used    NUMBER;
    nSpace_Alloc    NUMBER;
    nChain_Pct    NUMBER;
    for t in (select user owner, segment_name table_name from user_segments where segment_type='TABLE') loop
          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);
          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);
    end loop;
 -- 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
Last modified