Monday, August 4, 2014

ORA-01653, ORA-01658, ORA-01688, ORA-03233 even when there is free space in the tablespace.

ORA-01653: Unable to extend table %s.%s by string in tablespace %s
ORA-01658: Unable to create INITIAL extent for segment in tablespace %s
ORA-01688: Unable to extend table %s.%s partition %s by %s in tablespace %s
ORA-03233: Unable to extend table %s.%s subpartition %s by %s in tablespace %s
ORA-01654: Unable to extend index %s.%s by %s in tablespace %s
ORA-01683: Unable to extend index %s.%s partition %s by %s in tablespace %s
ORA-03234: Unable to extend index %s.%s subpartition %s by %s in tablespace %s

You may get the above errors, even if the is a lot of free space in the tablespace of the segment to be extended. This is because that free space is very fragmented.
When a segment asks for a new extent, the database will try to fit it in an equal or larger free extent. It will not fit it in more than one smaller free extents.
So, for instance, it the next extent to be created will be 2 MB and the largest available free extent is 1MB, you will get the above errors, even if the tablespace has gigabytes of total free space.

To check if this is the case, run the following query, which displays the free extents of a tablespace ordered by their size descending.
SELECT  C.NAME "DATAFILE NAME",
        A.FILE_ID "DATAFILE #",
        A.BLOCK_ID "BLOCK #",
        A.BLOCKS,
        ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) "MBYTES"
    FROM
        DBA_FREE_SPACE A,
        DBA_TABLESPACES B,
        V$DATAFILE C
    WHERE
        A.TABLESPACE_NAME = '[TABLESPACE NAME]'
        AND A.TABLESPACE_NAME = B.TABLESPACE_NAME
        AND C.FILE# = A.FILE_ID
ORDER BY 4 DESC;

If this is the case, you need to create larger free extents by reorganizing the tablespace or to add a new datafile, which at the moment it is created is a continuous free extent by itself.