Max extents reached

[kenny chia]

I have a segment that has its number of extents reached its maximum. This table is dynamic (alot of deletes and inserts). Is this a sign of impending trouble?

[twang]

ALTER TABLE "owner"."idx_name" STORAGE (MAXEXTENTS );

If the Prodution table reached maxextent frequently. To avoid I suggest reorging the table and calculated the size in mb that will be inserted into the table for the next 6months so that it can be allocated as the initial extent.

[kenny chia]

Thanks..
There are some problems in increasing the maximum number of extents for all the segments in my database, not too sure the reason. Are there any other ways to solve this problem?

[Yogeeraj_1]

hi,

You did not mention the version of Oracle Database you are using!

You should be using Dictionary managed tablespaces!

If you get the error "max extents", you need to alter the max extents for the table. If you have to do this on a given table over and over -- you need to rethink your extent size for that table. It might need to be larger.

In order to analyze if that problem would be solved by altering max extents -- you need only look at the error message. A good DBA might be proactive and run a query every now and then like:

select owner, segment_name, segment_type, extents, max_extents
from dba_segments
where max_extents-extents <= 3;

that'll tell you the objects that are getting near to failing.

If you get the "unable to extend" due to lack of space -- you need to increase the size of your datafile or add more datafiles.

I use maxextents unlimited typically with FIXED size extents (pctincrease = 0, initial = next for all objects in a tablespace and all objects use the same initial and next values).

See
http://technet.oracle.com/doc/oracle8i_816/server.816/a76965/c02block.htm#2775
for details on extent management.

If you have Oracle 8i, you should start using LOCALLY MANAGED tablespaces (LMT) with system managed extent sizes (UNLIMITED).

A locally managed tablespace with uniform extents makes that the "de-facto" rule, you cannot subvert it as you can with a dictionary managed tablespace.

- LMT totally avoids fragmentation -- it is impossible to have fragmentation in these tablespaces.
- LMT are much faster at allocating and de-allocating extents - many order of magnitudes faster.
- LMT decrease contention on the data dictionary.

hope this helps!
regards
Yogeeraj

[Jean-Luc Oudart]

You did not mention your oracle version.
But if you can go for LMT.

see this thread
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xb3fb5dc05a7ad711abdc0090277a778c,00.html

Rgds,
Jean-Luc

[kenny chia]

Hi
The Oracle version is 7.2

[Dave Walley]

I too have some databases on 7 and when a segment is growing rapidly, I would change the next extent and increase max extents. If you have a chance export the table, import it using the indexfile option, this will create a file that will allow you pre-create the segment. Then import the table with ignore=y you will then have the table in one neat extent.

Dave

[Volker Borowski · Accepted solution]

Kenny,

in 7.2 the maximum value of maxextens was related to the db_block_size.
With 2K it has been 121 with 4K it was 249 and with 8K it had been 505.

Once you bounced that one, no other way out than to reorganize the segment. So be sure to adjust NEXT in a corresponding way and be sure to have enough spare place to adjust.

You should upgrade at least to 7.3.3 which will give you the ability to use a higher maximum on max-extents.

Volker

[Brian Crabtree]

1. Check the next extent size for your objects. Something like the following might be a good idea:

select * from dba_segments where owner not like 'SYS%' and next_extent < (1024*500);

This will show you any objects with under 500k extent sizes.

2. Check your dba_tablespaces view. The default initial and next extent sizes define what sizes to use on objects without a set initial/next size. Increasing these to something like 1m for each can help keep new objects allocating a large number of extents on accident.

hope this helps,

Thanks,

Brian

[kenny chia]

Hi Volker
I think you answered part of my question (I can't increase the max extents). I guess I have a 7.2 database with block size 2K which will have a max extent limits of 121.

[malay boy]

Kenny,
7.2 cannot increase the extend ???.In this case then just export the table and import back but make the extend size bigger...
Well this might be a good idea because the tables have a lot of insert and deletes....

insert/delete definately will make your table defrag and only export/import will help you.

is coalesce exist in 7.2 ???? Surely 8 you can do manual coelesce...


good luck.

regards
mB

[kenny chia]

Hi mB
I get what you mean by coalesce. But Oracle 7.2 does not support Alter Tablespace coalesce

[Indira Aramandla]

Hi,

For a table that has frequent deletes and inserts, it has tobe re-organised every now and then. For this you can cron job the task to export and then import the table data say once every week.
For the first time when you export the table data, drop the table and re-create the table with good size of initial and next extents. This will prevent you table from extending into too many extents.

There are other oprions as well. But as you are usng Oracle 7.2, the export and import of table data should fix the issue of extending to many extents. Choose and convenient time when the table is is not beeing used then schedule the cron job.


IA

[Volker Borowski]

Hi Kenny,

this is bad news. So there is no way out than to reorganize. Indira listed the steps already. If you do not do this frequently, but only every now and then, be sure to assign only 100 for MAX-Extents in your reorg.

When you bounce this one, you can increase NEXT and set MAX to 110 and schedule your next reorg.
When you bounce 110 you can increase NEXT and set MAX to 115 and schedule an urgent reorg.
When you bounce 115 you can increase NEXT and set MAX to 119 and schedule a very very very urgent reorg and still have two spares left. :-)

Another thing you might consider is to use PCT_INCREASE, which will increase the NEXT size every time a new extent is allocated. But usually nobody likes this, because it requires a bit more of monitoring to avoid unexpected grow of segments.

Good luck
Volker