RE: [External] NEXT_EXTENT is too big to be allocated | SYSTEM tablespace
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mark.powell2@xxxxxxx>, <jithinsarath@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>, <Hemant-K.Chitale@xxxxxx>
- Date: Wed, 7 Dec 2016 20:56:14 -0500
So you have a few histogram definitions, do you? Holy cow.
I believe Mark Powell's advice should fix you up. Why this object is not in
SYSAUX, I'm not sure.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark
Sent: Tuesday, December 06, 2016 3:17 PM
To: jithinsarath@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx; Hemant-K.Chitale@xxxxxx
Subject: Re: [External] NEXT_EXTENT is too big to be allocated | SYSTEM
tablespace
An internal object (segment - Index) called I_HH_OBJ#_COL# which has
grown to have really big extents the largest extent size now is over 500MB,
and I believe the next extent request will be for 720MB based on the
next_extent value in dba_segments. <<
The above would be true only with a Dictionary managed tablespace since I do
not believe it is possible to convert a SYSTEM tablespace to being locally
managed. Alter the object storage parameters PCTINCREASE to 0 and NEXT to a
reasonable size if the tablespace is dictionary managed. However first
verify that your numbers are not really 500K and 720K.
_____
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Chitale, Hemant K <Hemant-K.Chitale@xxxxxx>
Sent: Tuesday, December 6, 2016 1:53:40 AM
To: jithinsarath@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: [External] NEXT_EXTENT is too big to be allocated | SYSTEM
tablespace
An extent that is "over 500MB" with SYSTEM allocation is strange. Was this
database created with a locally managed SYSTEM tablespace ? In what version
? What version are you currently running ?
How did you determine the "over 500MB" extent size ? From DBA_EXTENTS ?
What is the size of the Segment for this object (index) ? Query
DBA_EXTENTS for all the Extent Sizes (starting from Extent ID 0) for this
segment.
You do have the option to either increase the size of the SYSTEM datafile or
add another datafile to SYSTEM. But I would review the Segment and Extent
Sizes first. I don't know if you can use DBMS_SPACE.SPACE_USAGE against a
SYS index.
Hemant K Chitale
From: oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jithin Sarath
Sent: Tuesday, December 06, 2016 12:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: [External] NEXT_EXTENT is too big to be allocated | SYSTEM
tablespace
An internal object (segment - Index) called I_HH_OBJ#_COL# which has grown
to have really big extents the largest extent size now is over 500MB, and I
believe the next extent request will be for 720MB based on the next_extent
value in dba_segments.
Our DB block size is 8K and the free extents are capped at 520MB because of
this. Next time the object grows and Oracle has to increase extents, it will
look for a 720MB sized extent, but it will only find 520 MB of chunks. This
will result in an error obviously.
This is on the SYSTEM tablespace, which is locally managed and has SYSTEM
allocation type.
I am concerned about two things:
1. Does oracle adjust algorithm to look for smaller extents if a bigger
extent request fails.
2. If it doesn't, what are my options? I see that this is one of the
bootstrap objects in Oracle and can't really be tampered with. The index was
introduced in 7.3.3 so wondering if it's as simple as dropping and
recreating the index.
Any help is appreciated
This email and any attachments are confidential and may also be privileged.
If you are not the intended recipient, please delete all copies and notify
the sender immediately. You may wish to refer to the incorporation details
of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at
https://www.sc.com/en/incorporation-details.html
Other related posts:
