| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: estimating space saved as a result of reorganizing table
Hi Jared
Thanks .
I appreciate
regards
Hrishy
--- Jared Still <jkstill_at_gmail.com> wrote:
> 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-lReceived on Tue Aug 21 2007 - 01:25:26 CDT
 |
 |