1745804 Members
3838 Online
108722 Solutions
New Discussion юеВ

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