KB45464: "[ORACLE]ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error" error received due to large growth in database size, when trying to create empty Statistics tables

Symptom

In Strategy Enterprise Manager, during the process of Statistics table creation or upgrade (from Strategy Configuration Wizard or Strategy Enterprise Manager console), the following Oracle error message is seen:
 

ORA-01658 Unable to Create INITIAL Extent for Segment in Tablespace Error

 
On further investigation, it may be observed that the creation of empty Statistics tables with partitioning causes a substantial increase in the space used by the database.
 
This issue typically occurs if the tablespace is not big enough to accommodate the required initial extent, for the creation of all partitions. However, in this case it is seen that creation of empty Statistics ftables is consuming storage space in the order of gigabytes.
 

Cause

In Oracle databases, a value named INITIAL_EXTENT controls how much space is allocated to the table when the table created. In the case of partitions, each partition would count as a table.
 
The INITIAL_EXTENT value for a tablespace can be found by executing the following query:
 
 

select * from DBA_TABLESPACES

 
The amount of space used up on the database is controlled by the number of partitions being created and the value of INITIAL_EXTENT. For example, with a script that creates 180 partitions on a database that has the INITIAL_EXTENT as 65536 bytes, the space used would be 11.25MB. This can be calculated manually as:
 
 

180 * 65536 bytes = 11.25MB

 
 
In cases where the INITIAL_EXTENT is set to a very large value (example: 59652323 bytes), it is possible for empty partitioned Statistics tables to occupy a large amount of space. 
 
 

Action

Verify the INITIAL_EXTENT value and modify it to a smaller value.
 
The following formula can be used to estimate the amount of space required for the creation of empty partitioned tables:
 

number of partitions * INITIAL_EXTENT value = space required in bytes

 
WARNING
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.