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;
/

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: