RE: [External] NEXT_EXTENT is too big to be allocated | SYSTEM tablespace

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark
Sent: Tuesday, December 06, 2016 3:17 PM To: jithinsarath_at_gmail.com; oracle-l_at_freelists.org; Hemant-K.Chitale_at_sc.com 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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> Sent: Tuesday, December 6, 2016 1:53:40 AM To: jithinsarath_at_gmail.com; oracle-l_at_freelists.org 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jithin Sarath
Sent: Tuesday, December 06, 2016 12:45 PM To: oracle-l_at_freelists.org
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

--
http://www.freelists.org/webpage/oracle-l