Re: How does Oracle allocate new extent?

Check out dba_segments and you can see how many extents, bytes each table and/or index takes up takes up.

desc dba_segments :)

select owner||'.'||segment_name, bytes, extents from dba_segments where owner not in ('SYS', 'SYSTEM')
order by owner, segment_name

DBA_FREE_SPACE provides you with info on what kind of and how many extents you can allocate.

You can also try to coalesce the tablespace ALTER TABLESPACE cms01 COALESCE

and then query the dba_free_space again to see if any of adjacent extents have joined together to form one.

On Tue, 10 Aug 1999 12:10:20 GMT, mleung_at_earthlink.net (Wind) wrote:

>
>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
>

Andrey Dmitriev	 eFax: (978) 383-5892  Daytime: (917) 373-5417
AOL: NetComrade	 ICQ: 11340726 remove NSPAM to email