ORA-01653: unable to extend table by 8192 in tablespace

ORA-01653: unable to extend table <SCHEMA_NAME>.<SEGMENT_NAME> by 8192 in tablespace <TABLESPACE_NAME>

This error is quite common and it clearly means oracle was not able to allocate space to a segment and returned this error. Alert log file will show error similar to the following.

Wed May 31 17:15:28 2013 ORA-1653: unable to extend table SCOTT.TEST by 8192 in tablespace DATA_TBS

In this error message, “by 8192” means that a try of allocating 8192 blocks was made which failed. Actual size of the extent can be calculated by multiplying the number of blocks with the block size – if block size is 8192 (8k), the extent size would be 8192x8192 which is 67108864 bytes or 64 MB. If ALLOCATION_TYPE for the locally managed tablespace was AUTO (SYSTEM), this could be “by 1024” or “by 8” or could also be some other value; because it is internally decided by Oracle on how many blocks should be used to allocate next extent.

As stated above, mostly this error comes when tablespace is full and when DBAs see this error, they immediately add space to the tablespace (by adding a new datafile or extending existing datafiles), and problem is resolved most of the time.

Contiguous Space
It would be pertinent to mention that oracle needs contiguous blocks to allocate an extent. For example, if oracle needs to allocate an extent of 64 MB by allocating 8192 blocks (8K block size), these 8192 blocks should be contiguous.  

Dictionary Managed versus Locally Managed
After introduction of locally managed tablespaces, fragmentation is no longer an issue for the DBAs, but if someone is still using dictionary managed tablespaces, he can still face ORA-01653 because of fragmentation which may cause unavailability of contiguous free space in the datafile(s) to be used for new extents allocation and it would mean that ORA-01653 can still some even if you have a lot of free space available in a dictionary managed tablespace datafiles.

A Personal Experience
I would like to share my personal experience here where I faced same error for one of my tables where table size was above 400GB, tablespace (locally managed) size was around 1.5TB and free space in the tablespace was around 10GB. I was perplexed that why oracle was not able to allocate a chunk of 64 MB for my table regardless of still having 10GB free space in my tablespace. After investigation, I found that there were more than 100 datafiles in this tablespace; out of which many were 100% full and rest of them had less than 64 MB space available in them, but total free space added together in all datafiles was around 10G. So this made me realize that even if our tablespace has a lot of datafiles in it, we should make sure that there is always a few GB space available in at least one the datafiles  (by extending datafile, or adding new datafile) where large chunks of contiguous blocks remain available to fulfill the large extent allocation requirement.

Checking Contiguous Space

We can query DBA_FREE_SPACE to find out the free contiguous space available in the datafiles of a tablespace. For example, I have 3 chunks of contiguous space available in my TESTTBS which should be sufficient for me to avoid ORA-01653 because of unavailability of contiguous free space chunk. First query returns all files free available contiguous space. Second query shows if I have at least one file in TESTDBS which has free contiguous space less than 500 MB. Third query will show how many total chunks of what size I have per datafile.

SQL> select file_id,blocks,bytes/1024/1024 from dba_free_space where tablespace_name='TESTTBS';    FILE_ID     BLOCKS BYTES/1024/1024 ---------- ---------- ---------------          5      16256             127          5      58264        455.1875          5        768               6 SQL> select file_id,max(bytes/1024/1024) from dba_free_space where tablespace_name='TESTTBS' having max(bytes/1024/1024) < 500  group by file_id;    FILE_ID MAX(BYTES/1024/1024) ---------- --------------------          5             455.1875 SQL> select file_id,round(bytes/1024/1024,2) "Free Chunk Size MB",count(*) "Total Chunks" from dba_free_space where tablespace_name='TESTTBS' group by file_id,bytes order by 2;