Re: How do you defrag a tablespace?

Hi Phil,

The easiest way is to tackle the problem a table at a time using export with COMPRESS = Y, rename the table, import, validate the newly restored table, truncate and then drop the old table.

On the plus side this method is simple and provides insurance against the loss of vital
data.

On the minus side in the event that the table you wish to reorganize contains the foreign key for another table you will have to drop the referential integrity and then restore it after you are done. The second minus is that import is a slow operation.

There are better ways -- as I am sure other responses will point out :-) but this will work and as I said at the offset it is easy.

Do yourself a favor and buy a copy of the Oracle(8) DBA Handbook by Kevin Loney (Oracle Press)

Regards

Jerry

mdlcpgs_at_lhn.gns.cri.nz.nospam wrote:
>
> How do you defrag a tablespace? I did the check recommended in the
> tuning ref and got back bad answer. But the tuning ref is silent on how
> you fix it. (Extreme novice DBA).
> ----------------------------------------------------------------------------
> Phil Scadden, Institute of Geological and Nuclear Sciences
> Work: PO Box 30368, Lower Hutt Home: 55 Buick St, Petone, Lower Hutt
> New Zealand
> ph +64 (04) 569-9059, fax (04) 569 5016 ph (04) 568-7190,
 

-- 
Jerry Gitomer		Since I know how to spell DBA I became one. 
jgitomer_at_p3.net