Script To List Objects That Won’t Extend

Here is an Oracle DBA script to list objects that won’t extend due to no more room in the tablespace.
If the tablespace is set to autoextend (via the datafile clause(s)) this will not be a big issue. But if the datafiles are not set to autoextend, processes can stop until the DBA intervenes.
Use this script proactively.

/*
This script will list objects that won't be able to extend
due to insufficient space in the tablespace
*/

Select TableSpace_Name
, Owner
, Segment_Name
, Segment_Type
, To_Char((Next_Extent / 1024 / 1024), '999,999') || 'M' Nxt_Ext
From Sys.DBA_Segments A
Where Not Exists
(Select 'x'
From Sys.DBA_Free_Space B
Where A.TableSpace_Name = B.TableSpace_Name
And B.Bytes >= (A.Next_Extent * (1 + (A.Pct_Increase / 100)))
)
Order By TableSpace_Name, Segment_Name;

Output will look similar to:

TABLESPACE_NAME OWNER
------------------------------ ------------------------------
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
NXT_EXT
---------

XDB XDB
XDB$CHOICE_MODEL TABLE
1M

XDB XDB
XDB$COLUMN_INFO TABLE
1M