cancel
Showing results for 
Search instead for 
Did you mean: 

Max extents reached

SOLVED
Go to solution
kenny chia
Regular Advisor

Max extents reached

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?
All Your Bases Are Belong To Us!
13 REPLIES
twang
Honored Contributor

Re: Max extents reached

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

Re: Max extents reached

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?
All Your Bases Are Belong To Us!
Yogeeraj_1
Honored Contributor

Re: Max extents reached

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Max extents reached

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
fiat lux
kenny chia
Regular Advisor

Re: Max extents reached

Hi
The Oracle version is 7.2
All Your Bases Are Belong To Us!
Dave Walley
Frequent Advisor

Re: Max extents reached

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
why do i do this to myself
Volker Borowski
Honored Contributor
Solution

Re: Max extents reached

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

Re: Max extents reached

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

Re: Max extents reached

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.
All Your Bases Are Belong To Us!
malay boy
Trusted Contributor

Re: Max extents reached

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
There are three person in my team-Me ,myself and I.
kenny chia
Regular Advisor

Re: Max extents reached

Hi mB
I get what you mean by coalesce. But Oracle 7.2 does not support Alter Tablespace coalesce
All Your Bases Are Belong To Us!
Indira Aramandla
Honored Contributor

Re: Max extents reached

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

Never give up, Keep Trying
Volker Borowski
Honored Contributor

Re: Max extents reached

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