extent issue

[grace white]

Hi
I have a Oracle Database which has reached it's max extents of a 121. i have set this to unlimited but still get the same error message.

sql

alter tablespace default storage (maxextents unlimited);

when i query it with the following i get:

SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,
EXTENTS, MAX_EXTENTS
FROM DBA_SEGMENTS
WHERE MAX_EXTENTS - EXTENTS < 4
ORDER BY EXTENTS;

can anyone advise how to trouble shoot this

it appeared when i looked at the orignal tablespace it appeared with it changed ok.

select t.initial_extent,t.next_extent,t.min_extents,t.max_extents,t.pct_increase
from dba_tablespaces t
where t.tablespace_name = 'PMDB_NDX1';

regards
grace

[Jean-Luc Oudart · Accepted solution]

Hi Grace,

Could you post the exact error message ?
Check the alert log file for error messages too and post them as well.

Rgds,
Jean-Luc

[grace white]

Hi
PLease find enclosed if you look around
Tue Sep 30 16:54:03 2003.

It is there

Thanks
Grace

[Jean-Luc Oudart]

This is the index not the table
But same idea :

ALTER INDEX STORAGE ( MAXEXTENTS 400);
>>>>>>ADMUSER.NDX_TASKACTV_TASK
ALTER INDEX ADMUSER.NDX_TASKACTV_TASK
STORAGE ( MAXEXTENTS 400);

You can also change the extent size

Rgds,
Jean-luc

[Hari Kumar]

U have very good set of replies in your previous thread,their replies should work and very great info also given there.First thanks to them.

Can u please give the version info,Which tablespace(temp, rollback e.t.c) of your database.
Is that a table of Frequent delete/inserts e.t.c (i mean to come by steps)is that for a table ?
If it is 7.2, it depends on block size which restricts max extents (with 2K its 121,with 4K its upto 249 and if 8K upto 505).
One thing we can do is Export the related data, reorganise with (tablespace/table)with proper values and NEXT taken care and now import your data.

Thanks,

[Jean-Luc Oudart]

Grace,

ALTER INDEX ADMUSER.NDX_TASKACTV_TASK
STORAGE ( NEXT 4096K );

This is an example to chnage the next extent size to 4Mb.

Rgds,
Jean-Luc

[grace white]

Thanks Guys for pointing me in the right direction this is now resolved.

Regards
Grace

[Hari Kumar]

fine dude u have done it,
great !! how fast u r responding, i am just typing Click!! Jean with Good solution !! again typing Click!!! Grace had Done it !!
Thanks friends!! Go support Cheers!!

[Yogeeraj_1]

hi grace!
*
Once again, LMT will be the way to go! Locally managed tablespaces will be the solution and END of all the problems you are having and will continue to have in the future!
*
read:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#9633
*
below another useful quote from Thomas Kyte:

A dictionary managed tablespace, the only type of tablespace prior to Oracle8i, manages extents in a set of database tables. A locally managed tablespace does this extent managed in a bitmap in the header of a datafile. A zero bit means that space is free, a one bit means it is allocated.
-
Using the dictionary approach, we can see a lot of contention in the database when you do lots of extends. Dictionary updates are done serially (so one user at a time) and the SQL to get the free space can be somewhat time consuming. We need to query a free space table to find the BEST free extent that is as big or bigger then the extent you want. If we cannot find that, we have to go back and try to coalesce free space (rows in the free space table that are "adjacent" to eachother) and try again. When we finally find one, we have to delete it from free space and add it into allocated space.
-
Dictionary managed tablespaces allow for extents of any size which can (and frequently does) lead to free space fragmentation. That is -- you might have 500meg of free space in a tablespace but you find that your largest CONTIGOUS
free chunk is 1m and lots are less. This is a real problem when your next extents for objects in this tablespace are all greater than 1m. You might have 500meg free but no object will be able to extend.
-
Contrast this to a locally managed tablespace. Free space is managed in a bitmap at the head of the file. Instead of serializing ALL space requests for a database -- we serialize for a shorter period of time at the file. If you have
more then 1 file, you can have more then 1 space request being processed -- hence it removes contention. The process of finding free space is faster as well -- space is typically managed in a UNIFORM fashion in a locally managed tablespace -- each extent is exactly the same size as every other extent. No long search for the "best" fit -- the first fit is the best fit. So, its faster at finding space. Additionally -- free space coalescing happens automagically. Free up an extent and its bit goes to zero. If the bit in front of it and behind it were zero -- we now have 3 zero bits to indicate three free chunks.
-
Most importantly -- it is IMPOSSIBLE to have free space fragmentation in a locally managed tablespace with uniform extents. Since every extent is the size of every other extent -- ANY extent is the correct size for ANY objects next extent. If you have 500m free in a locally managed uniform tablespace -- you really do have 500meg free. In a dictionary managed tablespace, that same tablespace might be "full".
-
In Oracle8i, use locally managed tablespaces with uniform extents. Oh yeah, if someone says "but that'll cause our objects to have many extents" just say "so what, who cares". It is perfectly OK to have objects with many 100's of extents.

*
Hope this helps!
regards
Yogeeraj

[Hari Kumar]

Thanks alot from me too Yogeeraj !
Very great info!

Learning alot from forums.............