Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: estimating space saved as a result of reorganizing table

Re: estimating space saved as a result of reorganizing table

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 20 Aug 2007 09:15:38 -0700
Message-ID: <bf46380708200915o3e3325a7h35d184858179263c@mail.gmail.com>


On 8/20/07, hrishy <hrishys_at_yahoo.co.uk> wrote:

> I am wundering how do we estimate/compute how much
> space can be saved after a table reorganisation using
> dbms_rdefeination
>
>

Here's a start. This calculates approximately how much will be used for a table that is reorganized. You can take it from there to calculate the space savings.

Assume a table name of AGILE.BOM, and a future PCTFREE of 80%, and the statistics are current:

09:12:43 ordb02.radisys.com - js001292_at_dv07 SQL> l   1 select segment_name, 'TABLE' segment_type, bytes, bytes * (100/80) projected
  2 from dba_segments
  3 where owner = 'AGILE'
  4 and segment_name = 'BOM'
  5 and segment_type = 'TABLE'
  6 union
  7 select segment_name, 'INDEX' segment_type, bytes, bytes * (100/80) projected
  8 from dba_segments
  9 where owner = 'AGILE'
 10 and segment_name in (

 11     select index_name
 12     from dba_indexes
 13     where owner = 'AGILE'
 14     and table_name = 'BOM'

 15* )
09:12:48 ordb02.radisys.com - js001292_at_dv07 SQL> /
SEGMENT NAME                   SEGME            BYTES        PROJECTED
------------------------------ ----- ---------------- ----------------
BOM                            TABLE      128,450,560      160,563,200
BOM_IDX1                       INDEX       22,544,384       28,180,480
BOM_IDX2                       INDEX       31,981,568       39,976,960
BOM_IDX3                       INDEX       18,350,080       22,937,600
BOM_IDX4                       INDEX       22,544,384       28,180,480
BOM_IDX5                       INDEX       17,825,792       22,282,240
BOM_PK                         INDEX       33,554,432       41,943,040

7 rows selected.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 20 2007 - 11:15:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US