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

Oracle docs note this about ORA-01658:

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

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL.

The obvious cure for the ORA-01657 error is to:

1 - Make the INITIAL extent size smaller

2 - Empty the recycle bin

3 - Add a datafile the tablespace to provide a larger contiguous chunk size.

4 - Reorganize the tables in the tablespace

Try below query for your tablespace maximum chunk size

There are several views in the data dictionary that display database free space.  We have to start by defining free space:

• Is "free space" the empty space in the tablespace?
• Is "free space the available space on the data files and filesystems?

There are several salient DBA views for displaying free space in Oracle:

• dba_free_space
• dba_data_files
• dba_tablespaces

Here is a simple script to display Oracle free space within the data file space.  Note the in-line view where dba_data_files is joined into dba_free_space:

SELECT
   a.tablespace_name,
   a.file_name,
   a.bytes allocated_bytes,
   b.free_bytes
FROM
   dba_data_files a,
   (SELECT file_id, SUM(bytes) free_bytes
    FROM dba_free_space b GROUP BY file_id) b
WHERE
   a.file_id=b.file_id
ORDER BY
   a.tablespace_name;

This shows the allocated and free bytes within the data files, but it DOES not show available free space on the OS filesystem:

TABLESPACE_NAME      FILE_NAME                 ALLOCATED_BYTES     FREE_BYTES

--------------------                -------------------------              ---------------                        --------------

TBS_LOCALS           /u01/app/oradata/devdb/de         20,971,520                        20,774,912

                                vdb/devdb_tbs_locals_01.dbf

                     

Note that there are many other ways to create tablespace reports.  This method create an intermediate view to doisplay free space:

Rem free_space.sql

rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;

CREATE VIEW SYS.FREE_SPACE AS
SELECT
       TABLESPACE_NAME TABLESPACE,
       FILE_ID,
       COUNT(*)    PIECES,
       SUM(BYTES)  FREE_BYTES,
       SUM(BLOCKS) FREE_BLOCKS,
       MAX(BYTES)  LARGEST_BYTES,
       MAX(BLOCKS) LARGEST_BLKS
FROM  
       SYS.DBA_FREE_SPACE
GROUP  BY TABLESPACE_NAME, FILE_ID;

This is the next script, whixch show all free space within the tablespace:

rem tsfree.sql - Shows all free space within tablespaces.

Prompt be sure that you have run free_space.sql prior to this script

clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;

column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME        FORMAT A15    HEADING 'Tablespace';
COLUMN PIECES                 FORMAT 9,999  HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES            FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES            FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE               FORMAT 999    HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE    FORMAT 999    HEADING 'Percent|FREE|Contiguous';

ttitle left datevar right sql.pno -
       center ' Instance Data File Storage' SKIP 1 -
       center ' in ORACLE Megabytes (1048576 bytes)' -
       skip skip;

BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT

select to_char(sysdate,'mm/dd/yy') today,
       TABLESPACE_NAME,
       PIECES,
       (D.BYTES/1048576) FILE_MBYTES,
       (F.FREE_BYTES/1048576) FREE_MBYTES,
       ((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
       (F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
       ((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
      D.FILE_ID= F.FILE_ID AND
      D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;

Here is the report from this script.

Tablespace         Pieces     Mbytes  Mbytes  FREE       Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS       1      18         2        10            2   10
MASTER1_DETAILS       1      20        20       100           20  100
MASTER2_DETAILS       1       2         1        65            1   65
MASTER3_DETAILS       1       5         5        95            5   95
MASTER4_DETAILS       2       3         1        36            1   35
RBS_ONE              11     490       380        78          280   57
RBS_TWO              11     490       379        77          279   57
SYSTEM               17      60        45        76           45   75
TEMP                  1     650       650       100          650  100
TOOLS                 2      15         9        61            8   55
USERS                41     100        31        31            4    4
----------
                      13,255

This report is useful for finding the largest sized chunk of free space within a tablespace.

This case of ORA-01658 concerns needing to make the tablespace bigger, which can be done by either extending your file, or adding another one.  This is a good solution for ORA-01658 because repository tables need to be meticulously created keeping the extents of the table in mind.