Re: Need help with defraging tablespace.

The NiteFrog.- wrote:
>
> Hello All,
>
> Here is the query to be able to tell you if your tablespaces are
> fragmented:
>
> select tablespace_name, sum(bytes), max(bytes), count(tablespace_name)
> from dba_free_space
> group by tablespace_name
> order by tablespace_name;
>
> Now I know that sum() if the total size of free space left in the
> tablespace and the max() and count() not to sure. Yes I am a rookie
> DBA trying to learn th eropes so please bear with me. Then it is
> mentioned the if the contiguous is greater than 10 to 15 you should
> defragment the tablespace. So how do you tell? Do you subtract
> max-sum? I just dont get it.
>
> Thank again,
>
> Kev.-

Some 'simple' steps are in order here...

1. Come up with a small list of sizes that you think will be adequate for your db, say 1m, 10m, 100m, 1g
2. Align each object you have to one of the above sizes, don't worry too much about extents, as long as everything is below (say) 50 or extents then there is nothing to worry about
3. Recreate your tablespaces as LOCALLY MANAGED with the uniform sizes as in (1)
4. Reload your data
5. forget about fragmentation - its a thing of the past...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse