Monitoring database free space
FREE_SPACE.SQL
SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(seg.max_extents, '999,999,999') || chr(13) || chr(10) ||
'Problem: Max extent reached'
FROM sys.dba_segments seg
WHERE seg.extents = seg.max_extents
UNION ALL
SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(t.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_tables t
WHERE seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name
AND seg.owner = t.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = t.tablespace_name
AND free.bytes >= t.next_extent
)
UNION ALL
SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(i.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_indexes i
WHERE seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name
AND seg.owner = i.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = i.tablespace_name
AND free.bytes >= i.next_extent
)
UNION ALL
SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(c.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE seg.segment_type = 'CLUSTER'
AND seg.segment_name = c.cluster_name
AND seg.owner = c.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = c.tablespace_name
AND free.bytes >= c.next_extent
)
UNION ALL
SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(r.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_rollback_segs r
WHERE seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name
AND seg.owner = r.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = r.tablespace_name
AND free.bytes >= r.next_extent
)