Re: Could someone please give me a hand with this...

nitefrog_at_yahoo.com (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.-
>

When you defrag a tablespace depends on your object space allocation and tablespace management policy. If you define evey object in the tablespace with the same storage clause where initial = next, pctincrease = 0 and all extents are one size then you may never need to defrag the tablespace because every free extent is the exact size that an object will attempt to allocate. You then just manage by making sure free space for extents is available in the tablespace.

It is when you have objects with different extent sizes in the tablespace and drops and/or reorganizations create free space extents that are of different sizes that fragmentation may become a series problem. It is actually only a problem when objects fail to extend due to a lack of contiguous free space and the sum of the free space extents exceeds the amount requested. Otherwise the problem is you do not have enough space allocated to the tablespace.

Ideally a tablespace will have only one free extent that includes all the free space in the tablespace. But it your tablespace consists of five files then you are probably going to have 5 free extents minimum since it can be very difficult to make the allocation requests come out perfect. I would suggest you manage your tablespace based on minimum free space as a percentage of allocated space to the tablespace rather than number of free extents. Calculating the sum of the number of extents equal to the largest allocation that will be requested within the tablespace divided into the free space extents available will give you an excellent measure of how much useful space a tablespace has.

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Sat Jul 08 2000 - 00:00:00 CDT