Segments with many allocated extents
This is regarding the number of allocated extents in any segments.
Our environment is SAP ECC 6.0 in ORACLE (188.8.131.52.0) in AIX.
We are having 2218 allocated extents for table BSIS in the tablespace PSAP<SID>.
The table is 114GB in size and have the default value 2147483645 as max.extents.
Like the same we have around 30 to 40 tables having more than 200 allocated extents.
The CCMS monitoring templates have all these tables in RED for the node 'Most allocated extents in any segment'.
I have tried online reorg for some of these tables and the number of extents gets increased at times.
How can we decrease the number of extents for such tables.
I'll put my 2c here.
I'm quite sure your table is located in a Locally Managed Tablespace (if you do not know what that is, please, check the oracle documentation)
In a LMTS, Oracle can decide the size of the extent (autoallocated) or you create a tablespace where ALL objects will have the same size (UNIFORM)
SAP uses the autoallocated, therefore Oracle makes the decision of the extent size and will change it if the table growth.
For that reason
1) reorganization of the table may or may not end with different number of extents, it is a decision taken by oracle, not by the DBA
2) having a lot of extents is not a problem (unless you are in an old Dictionary Managed Tablespace, which should not be the case)
see note 825653 Oracle: Common misconceptions, point 19
IMHO, trying to reduce the number of extents in a LMTS tablespace is a "waste" of time as there is problem to be solved
But you can plan the next extents correctly.
Not, you can't if you use the standard SAP ( autoallocate, Oracle decision)
You can plan to make different tablespaces with different UNIFORM sizes, but how do you decide for the "correct" size? a lot of work to solve a non existing problem in LMTS (too many extents)