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

When inserting records into Oracle database by SQL statements, creating new tables, importing backup dump into Oracle database or when manipulating tables or data in the Oracle database, the following error may occurs:

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

The problem is caused by the Oracle unable or fails to find sufficient contiguous space to allocate INITIAL extent for segment being created, due to the data files for a tablespace specified in tablespace_name has become full, or there is not enough space in the datafile.

You can check if the datafiles available and used by Oracle’s tablespaces is autoextensible, and if there is any free space in the datafile by using the following query.

select a.file_id,b.file_name,b.autoextensible,b.bytes/1024/1024,sum(a.bytes)/1024/1024
from dba_extents a , dba_data_files b
where a.file_id=b.file_id
group by a.file_id,b.file_name,autoextensible,b.bytes/1024/1024

The solutions or workarounds for the ORA-01658 Oracle error include:

1. Add a new datafile into the tablespace to increase additional space by using SQL query as shown:

   ALTER TABLESPACE <tablespace_name> ADD DATAFILE <datafile_name7gt; size <size>

2. Retry the transaction or process with a smaller value for INITIAL.
3. Set AUTOEXTEND on for the data file of the tablespace.
4. Increase the size of the existing datafile by using the following SQL command:

   ALTER DATABASE DATAFILE <datafile_name> RESIZE newsize;

Related Articles

• How to Remove and Drop Datafiles from Tablespace in Oracle Database
• ORA-25153 Temporary Tablespace is Empty Error in Oracle
• How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile
• How to Rename or Move Oracle Tablespace Datafile to Another Location
• Oracle ORA-14074 Create or Add New Partition Fails Error
• Create, Add or Split Oracle Database Partition Fails with ORA-14080 Error
• Oracle EXP-00091 Error When Export Database
• Oracle Database Import Error 3113/3114
• ORA-02449 Oracle Drop Table Error
• Workaround to Fix Windows 7 Unable To Create or Save New Files in the Folder Error When Unloading Box

This entry was posted on Thursday, December 7th, 2006 at 3:29 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.