Segments that cannot extend or have reached max extents

clear buffer
clear columns
clear breaks
set verify off
set pause off
set timing off
set pagesize 200
set linesize 80

Column Segment_Name Format A30 Heading “Segment”
Column Owner Format A30 Heading “Owner”
Column Segment_Type Format A20 Heading “Type”
Column Tablespace_Name Format A30 Heading “Tablespace”
Column Nxt_Ext Format A10 Heading ” Next| Extent”

spool not_extd.lis

Select TableSpace_Name, Owner, Segment_Name, Segment_Type,
To_Char((Next_Extent * (1 + (Decode(Extents, 1, 0,
A.Pct_Increase) / 100))/1024), ‘999,999’) || ‘K’ 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 + (Decode(Extents, 1, 0,
A.Pct_Increase) / 100))))
Order By TableSpace_Name, Segment_Name;
spool off;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: