Re: reorg of Oracle10g tablespace in offline mode

On Aug 12, 5:53 am, Joe <joe1..._at_tlen.pl> wrote:
> I just dropped varchar2 (50) column full of data on a couple of tables
> (approx 3 millions of records total) in my tablespace, and now I would
> like to "defragment" the 10g database.
> Toad 8 that I use shows tablespace Fragmentation Index = 33.57 and
> when I look at tablespace map, it has many free space pieces spreaded
> around whole tablespace as well as data of many tables spreaded too.
> I would like to reorganize this tablespace (I can do it offline). Can
> you please quide me with that?
> I would also like to know the Fragmentation Index.. 33 is high
> defragmentation or fair enough?
>
> Thank you.

There's no command to do what you want to do (which is, presumably, to squish all the bits of remaining tables together leaving a nice big chunk of empty space at the end of the tablespace).

If you are going to create new tables in this tablespace; or if the existing tables are going to grow over time, then I wouldn't necessarily worry about it, because Oracle will slot those new bits of storage into the empty space vacated by the old table. The free space will, eventually, get used.

If it really is an issue, though, the only real way to achieve what you're after is to alter table X move tablespace Y... and to do that for every segment resident in your existing tablespace, until such time as the existing tablespace is empty, and you can drop it. That is: create a new tablespace, move everything out of the old and into the new; drop the old.

But it's an awful lot of work for a bit of disk space... Received on Sat Aug 11 2007 - 22:00:51 CDT