We have some shedulars to load data in to tables in daily basis . Some times we get below error though we have enough space in tablespace and this cause loader failure .
ORA-1653: unable to extend table RBA.DT_TABLE by 12800 in tablespace RBA_DATA .
In the above case we have 400 MB freespace in RBA_DATA
tablespace , still we get the error .
Is there any way to monitor or predict this one or two days in advance so that we can take care about the situation .
I guess your tablespace is fragmented. It might have free space but can't find the extent size(chunk size of 12800) its trying to allocate. You have to reorg or add more space/datafiles.
make the table & tablespace pctincrease to 0.
You may have free space but do u have contiguous space , that is contiguous space enough to hold next extent (12800 in your case).
The space may be highly fragmented.
Try tablespace coalescing.
Find out the total number of extents with max free space size is > next extent , if it is less than 5 , need to work on tablespace.
Use the following methods.
1) ALTER TABLESPACE COALESCE;
2) ALTER DATABASE DATAFILE 'filename with path' AUTOEXTEND ON;
run the following query :
you will see the largest available extent in tablespace and no.of extents it is fragmented into:
select tablespace_name,sum(bytes) "Free bytes", max(bytes) "Largest Extent",count(block_id) "# extents" from dba_free_space
group by tablespace_name;
Hope it helps you.
Be carefull while coelsing as it makes the next extent as the size of the whole tablesize.