Re: extents exxceeded ?? ( need help )

yung-feng chang me stnt (yxc1612_at_hertz.njit.edu) wrote:
: Dear netters:
 

: One user get following message while he is running a query.
 

: ORA-01556 maximum number of 99 extents exceeded.
 

: I check the free space for the tablespace first; It still have
: 1.2G available!!
 

: Then, I try to allocate more extents for tables and tablespaces that
: he used. I increase max extents for the tablespace from 99 to 150, then
: I run his query again. I find the error message become:
 

: ORA-01556 maximum number of 121 extents exceeded.
 

: No matter what max extent or extent size I increase, it seem that
: 121 is "ceil"??
 

: I find lots fragnments on that tablespace. Is this cause problem?
: If so, how to remove those fragnments. If not, what cause this problem?

The maximum number of extents is operating system dependent. In a VAX/VMS environment, for instance, 121 is the maximum number of extents permitted (even though you may have specified a larger value for the object within Oracle). Ideally, your objects should have as few extents as possible. Too many extents implies an improperly sized object with a lot of fragmentation and warrants a re-evaluation of INITIAL extent, NEXT extent and PCTINCREASE. The Oracle RDBMS Database Administrators Guide discusses fragmentation, and different approaches for cleaning up such problems.

You did however mention that this problem occurred during a *query*. You did not mention what this query does but we can make a few assumptions. If your query does not explicitly create any objects (i.e. a temporary table to hold intermediate results), the problem may be in the tablespace defined as your temporary tablespace. This tablespace is used by Oracle to handle temporary storage during sorting, etc., using whatever default storage parameters were defined for that tablespace. You would probably want to allocate a separate tablespace for this purpose, with appropriately size storage parameters (consider a nonzero value for pctincrease).

If your query does create objects, look at the storage parameters for those objects. Also check you rollback segments to see if their total extents have limited out. I would recommend you run a query against DBA_EXTENTS along the lines of:

   SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME,    COUNT(*) EXTENTS, SUM(BLOCKS) BLOCKS
   FROM SYS.DBA_EXTENTS
   GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME This will give you a report of the objects in your database, and the number of extents assigned to each. You can then determine where your potential troublespots are and take steps to correct them. This is undoubtedly a subject for great debate, but I would flag anything over 10 extents worthy of further consideration (actually, I get skittish of anything over 3).

Hope this helps.

--
===========================================================================
Kevin J. Lukes   Programmer/Analyst |  klukes_at_novatel.cuc.ab.ca
NovAtel Communications Ltd.         |  (403)295-4573
Calgary, Alberta CANADA             |  "Opinions expressed are my own"