Re: Best way to unfragment a table?

Rose,

Here is another way to defragment a tablespace.

Lets say your datafile is 400M in tablespace TS1 and the table is about 80M.

You can increase the tablespace size temporarily by using:

alter database datafile '/r1/prod/...' resize 500m;

Rename table to something else.

import the table from export dump file.

check the new table count(*) with the old table count(*)

drop the old table.

alter tablespace TS1 COALESCE;

alter database datafile '/r1/prod/...' resize 400m;

Good luck !!!

Oracleguru
www.oracleguru.net
oraclegur_at_mailcity.com

skubiszewski_at_Eisner.DECUS.Org wrote in article <F79HE9.97q_at_news.decus.org>...
> In your experience, what's the best way to unfragment a table?
> I'm debating the best way to reduce a table with many extents.
>
> Would you...
> Export/import the table using compression?
> Create a new table and copy the data?
>
> I was planning to try the second method but quickly
> realized that there are sequences, triggers, indexes and
> constraints to consider.
>
> The export/import seems easier because it should
> maintain the relationships amongst objects.
>
> Your experiences?
>
> Rose
>
Received on Wed Feb 17 1999 - 11:43:48 CST