To check Tablespaces Approaching Max Extents


Approaching Max extents
different formula (less than 500 extents to extend)

select
owner, SEGMENT_NAME, segment_type, tablespace_name, NEXT_EXTENT,PCT_INCREASE,
INITIAL_EXTENT, EXTENTS,
MAX_EXTENTS  from dba_segments  where tablespace_name= ‘APPLSYSD’ and
extents-max_extents<500;

select
count(*) from dba_segments  where
tablespace_name= ‘APPLSYSD’ and extents-max_extents<506;

Approaching Max extents
different Formula 2*extents>Maxextents

select
owner, SEGMENT_NAME, segment_type, tablespace_name, NEXT_EXTENT,PCT_INCREASE, INITIAL_EXTENT,
EXTENTS,MAX_EXTENTS  from
dba_segments  where
tablespace_name=’&tbs’ and 
2*extents>max_extents;

Less than 25 extents able to
extend

select
owner, segment_name, segment_type, initial_extent, next_extent, extents,
max_extents,
pct_increase from dba_segments where tablespace_name=’&TS_NAME’ and
max_extents-extents<25;

Checking total space
available within TS (Effective MB)

select
tablespace_name, sum(bytes/1024/1024) “mb” from dba_free_space
group
by tablespace_name having tablespace_name=upper(‘&tname’);
Recent Posts