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
