I came across the following code that lists all tablespace objects with growth problems (specifically, ones that will fail when they try to grab another extent). It works for both Locally-Managed and Dictionary-Managed tablespaces (in the case of locally-managed that use AutoAllocate extent management, the script assumes that all the extent sizes are the same):
Set Linesize 130
SET Linesize 130
SET Trims on
COLUMN Tablespace Format A30
COLUMN SegmentType Format A12
COLUMN Owner Format A12
COLUMN Segment Format A30
COLUMN "Required Extent(KB)" Format 999,999,999.99
COLUMN "MaxAvail(KB)" Format 999,999,999.99
SELECT /*+ RULE */
seg.tablespace_name "Tablespace", seg.segment_type "SegmentType",
ext.owner, ext.segment_name "Segment",
DECODE
(freespace.extent_management,
'DICTIONARY', seg.next_extent,
'LOCAL', DECODE (freespace.allocation_type,
'UNIFORM', freespace.initial_extent,
'SYSTEM', ext.BYTES
)
)
/ 1024 AS "Required Extent(KB)",
freespace.largest / 1024 "MaxAvail(KB)"
FROM dba_extents ext,
dba_segments seg,
(SELECT /*+ RULE */
maxsize_perfile.tablespace_name, tbs.extent_management,
tbs.allocation_type, tbs.initial_extent, tbs.next_extent,
MAX (maxsize_perfile.maxsizebytes) AS largest
FROM (SELECT /*+ RULE */
ddf.tablespace_name, ddf.file_id,
DECODE (autoextensible,
'YES', (ddf.maxbytes - ddf.BYTES),
0
)
+ NVL (MAX (dfs.BYTES), 0) AS maxsizebytes,
NVL (MAX (dfs.BYTES),
0
) AS maxfreeextentsizebytes
FROM dba_free_space dfs,
dba_data_files ddf,
dba_tablespaces tbsp
WHERE dfs.file_id(+) = ddf.file_id
AND tbsp.tablespace_name = ddf.tablespace_name
AND tbsp.CONTENTS = 'PERMANENT'
AND tbsp.status = 'ONLINE'
GROUP BY ddf.tablespace_name,
ddf.file_id,
DECODE (autoextensible,
'YES', (ddf.maxbytes - ddf.BYTES),
0
)) maxsize_perfile,
dba_tablespaces tbs
WHERE maxsize_perfile.tablespace_name = tbs.tablespace_name
AND tbs.status = 'ONLINE'
GROUP BY maxsize_perfile.tablespace_name,
tbs.extent_management,
tbs.allocation_type,
tbs.initial_extent,
tbs.next_extent) freespace
WHERE seg.owner = ext.owner
AND seg.segment_type = ext.segment_type
AND seg.segment_name = ext.segment_name
AND seg.tablespace_name = ext.tablespace_name
AND (seg.extents - 1) = ext.extent_id
AND seg.tablespace_name = freespace.tablespace_name
AND DECODE (freespace.extent_management,
'DICTIONARY', seg.next_extent,
'LOCAL', DECODE (freespace.allocation_type,
'UNIFORM', freespace.initial_extent,
'SYSTEM', ext.BYTES
)
) > freespace.largest
ORDER BY seg.tablespace_name, seg.segment_type, seg.segment_name