| 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
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'
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 Mon Aug 20 2007 - 11:15:38 CDT
 |
 |