Re: How to shrink a table?

> The tables are fragmented due to the deletions you have performed to get
> your space back
> and therefore have rows in blocks in most or all of the extents they have
> grown into. In order to
> shrink the tables back into fewer extents, you will have to re-organize the
> tables by:
> 1) create temp_tab as select * from orig_tab;
> 2) truncate orig_tab;
> 3) insert into orig_tab select * from temp_tab;
>
> If you don't have the space in the tablespace for the temp_tab just specify
> in the create table statement
> another tablespace!!!

It will help but it is not the solution.The solutions is to perform an export the data, rebuild the tables (possibly the entire tablespace) and reimport the data. If you use the solution you suggest you will just fragment the tablespace.

To obtain an optimized solution one would need to drop the tablespace and recreate it based on statistics obtained by using ANALYZE on the tables. Then rebuild the tablespace making sure that the default PCTINCREASE is equal to 1 which will force SMON to coallesce extents. Then rebuild the tables using extent sizes that correspond with the tables actual usage statistics.

Regards,

Daniel A. Morgan Received on Wed Aug 05 1998 - 23:15:20 CDT