ORA-01658: unable to create INITIAL extent for segment in tablespace tablespaces has 16GB freespace in each

Most of the sites and blogs offered this solution.

But my problem is quite different.

the solution: there are a number of ways that Oracle suggests you can do to remedy this issue

• add a new datafile into the tablespace to increase space. example:

  ALTER TABLESPACE SYSTEM ADD DATAFILE 'c:/oraclexe/oradata/xe/system2.dbf' size 25M

• set AUTOEXTEND on for the data file for the tablespace. example:

  ALTER DATABASE DATAFILE  'c:/oraclexe/oradata/xe/system2.dbf' AUTOEXTEND ON;
  

• increase the size of the existing datafile. example:

  ALTER DATABASE DATAFILE 'c:/oraclexe/oradata/xe/system2.dbf' RESIZE 25M;
  

i actually tried the autoextend but that did not remedy the issue for me. i then added a datafile to the tablespace. that seemed to work.
note: if you’re using windows, remember to turn your file separator around from a / to a if you are using the SQL Command Line. you can see this in the examples above.






We have given scripts to create tablespaces, tables and indexes to one of the client in Brazil.



Once the tablespaces,tables are created, when the index creation script run, the above error is getting.


The table contains 366 partitions and 64 sub partitions. And we suggest deferred_segment_creation to false.

And also customer asked to create tablespaces with 100M intial and Next staorage parameters.

So I have created tablespace with uniform size 100M and size 500M autoextend on.

There is no data in the table. 1 index has been created and it is consuming 2.3TB space. While creating second index we are facing the above mentioned error. 

Nothing but Oracle parameter deferred_segment_creation default value is true. We made it false. Nothing but Oracle is creating extent in each sub partition with 100M size and over all it is consuming 2.3TB for jus creating index.

We have given 2 options to the client.

1. deferred_segment_creation parameter to TRUE.
2. create the tablespace with size 16M autoextend on ...... extent management local uniform 1M.

Client has selected the second one.