Re: How does Oracle allocate new extent?

Michael,
dba_free_space contains all unallocated extents. In your case, I think that the bigger free extent in your tablespace is smaller then 10 M.

This query count the number of free extents in your tablespace select count(*)
from dba_free_space
where tablespace_name = 'CMS01';

This query show the bigger free extent in your tablespace select max(bytes)/(1024*1024)
from dba_free_space
where tablespace_name = 'CMS01';

Change the NEXT storage clause of your table EPISODE to be able to allocate an extent smaller or equal to the largest free extent :

alter table episode
storage (next XXXX);

Perhaps you need to coalesce all the free extents in your tablespace, if the largest is very small.

Good luck

Wind a écrit dans le message <37b0150a.19771705_at_news.earthlink.net>...
>
>hey guys,
>
> I have a question of how oracle allocates new extent.
>
> In my case, I have 60 MB left in datafile cms01 where includes
>table EPISODES. The next extent for table EPISODES is 10 MB, so it
>shouldn't be a problem, but in reality the oracle engine couldn't
>allocate the 10 MB from the 60 MB in the datafile cms01 and produced
>an oracle error in the alert log.
>
> I found out that Oracle needs continuous space for the extent.
>My question is how can I tell if there is fragmentation by looking
>dba_free_space? Or, do I need to look at other views as well?
>
>thanks in advance.
>
>Michael
>WindyCloud
>7k_at_gamestats.com
>
>Realms of the Kingdoms, http://7k.gamestats.com
>
Received on Tue Aug 10 1999 - 09:10:02 CDT