Newsgroups: comp.databases.oracle.server
Subject: Re: Am I running out of extents (fragmentation)?
References: <32F79EE3.713E@mda.ca> <3300B6A7.748E@impsat1.com.ar>
Claudio Roca writes:
>Simon Goland wrote:
>>
>> Two tablespaces are giving problems to a user who cannot create more
>> tables. I ran the following query
>>
>> Looking at the 'Free extents' I concluded that there is quite a bit of
>> fragmentation. So the solution is to do an export, drop the tablespaces,
>> recreate and import. Am I right? Is there anything else I should check
>> prior to this operation? It is somewhat strange to me because the
>> datafiles for these tablespaces are big - 2GB and 900MB (in the order
>> they appear above), and I don't think there is a lot of data in yet.
>> Maybe I should also modify some storage parameters for the tablespaces?
>>
>> Thanks,
>> --
>> [ Simon Goland B-)> sg@mda.ca ]
>> [ Without action there is no change ]
>If your datafiles are very big i would suggest you not to drop the
>tablespace but export the tables and drop them, then import them and the
>tablespace wil have one contiguous block of free space.
>I think there's no need to drop the tablespace.
>Hope this will help you.
Claudio,
If you have Oracle 7.3, you can eliminate free space fragmentation on the
fly, while users are using the database! You can type:
alter tablespace XXX coalesce;
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: www.arikaplan.com <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page