Re: How to shrink a table?

David,

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!!!

I hope this helps!!

Nevin Hahn
ICG Communications

David Rueter wrote in message <6pov5d$k5o$1_at_news01.deltanet.com>...
>I have an Oracle 7 tablespace that is nearly full.
>
>Rather than adding a file to the tablespace, I have deleted some unneeded
>rows from some tables.
>
>However, the sizes of the tables do not change when I delete the rows.
>
>I guess tables grow automatically, but don't automatically shrink. Is this
>right?
>
>In any case, how can I reduce the size of (shrink) individual tables?
>
>David Rueter
>drueter_at_assyst.com
>
>
Received on Thu Jul 30 1998 - 02:02:48 CDT