Monitor your database for objects for which the RDBMS will fail to allocate future extents.
1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):
SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB, S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB, autoextensible_bytes/(1024*1024) autoextend_MB FROM SYS.DBA_SEGMENTS S, (SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes from dba_data_files group by tablespace_name) d WHERE S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes ) AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and f.tablespace_name=d.tablespace_name;
2. Objects that are near their MAXEXTENTS (ORA-1632):
select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE='INDEX'
AND I.max_extents < 200000 and I.owner not in
('SYS','OUTLN','SYSTEM','TOAD')
AND I.MAX_EXTENTS - S.EXTENTS < 10;