Re: Defragmentation

In 8.x if the table is partitioned, you can basically defrag the table without dropping it.

In 7.x you are really stuck, you have to drop all referencial constraints (save them).
export the table with exp compress=y (that's the default) drop the table,
import the table back in.

re-create the constraints.

Check to see if you have this option is 7.x (I can't remember, and I don't have access to a 7.x database to test.)

export table xxxx data.

disable all foreign key constriants.

delete from table xxxx;
alter table xxxx deallocate unused extents; alter table xxxx storage(next {newsize});

import data back in

re-enable constraints.

Himanshu Gupta <himanshu.gupta_at_luton.ac.uk> wrote in message news:37E26D4F.9C6B6135_at_luton.ac.uk...
> Hello.
>
> One of the tables in our database is highly fragmented.
> To improve the performance, we have been advised to de-frag
> the table. This table has many constraints and is used extensively in
> running of other applications.
>
> Now I have following questions:-
>
> 1. Is it possible to de-frag a table without defragmenting the
> whole database?
> 2. Do I have to shut down the whole database in order to
> defrag the table? How?
> 3. What are the steps involved in degragmenting it.
>
> We are using Oracle 7.3.2.3.2 and PL/SQL release 2.3.2.3.1
>
> I would appreciate if you can send me any helpful comments (even short
> notes will do)at my following email addresses:-
>
> himi_at_luton.ac.uk
> himi_at_himanshu.freeserve.co.uk
>
>
> Many thanks.
>
> Himanshu.
Received on Fri Sep 17 1999 - 21:53:45 CDT