[SOLVED] java.sql.SQLException: ORA-01658: unable to create INITIAL extent for segment in tablespace

ISSUE

ORA-01658: unable to create INITIAL extent for segment in tablespace USER_DATA

REASON

If there is no enough space in the tablespace (e.g: USER_DATA), it will throw this error.

SOLUTION

We are trying to extend the space using 'autoextend' instead of setting fixed size. Login as sys_dba user and follow the below steps.

STEP 1

Find the list of tablespaces already available by below query.

select 'alter database datafile '|| file_name|| ' '|| ' autoextend on maxsize unlimited;' from dba_data_files;

You will get the below results:

alter database datafile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\LOB_DATA  autoextend on maxsize unlimited;

alter database datafile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\USER_DATA  autoextend on maxsize unlimited;

alter database datafile C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INDEX_DATA  autoextend on maxsize unlimited;

STEP 2

Run the below query which will set the tablespace to autoextend whenever the actual space is filled.

alter database datafile 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\LOB_DATA'  autoextend on maxsize unlimited; alter database datafile 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\USER_DATA'  autoextend on maxsize unlimited; alter database datafile 'C:\ORACLEXE\APP\ORACLE\PRODUCT\11.2.0\SERVER\DATABASE\INDEX_DATA'  autoextend on maxsize unlimited;