| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Defragmentation
Bob Fazio wrote:
>
> 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.
Before you derag, take a look at the white papers on www.europa.com/~orapub
You may find that you don't need to defrag it all...
"Everything must be in one extent" is a popular myth...
Cheers
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Sep 17 1999 - 20:45:42 CDT
 |
 |