To check the Segments Approaching Max extent in specific tablespaces

set
linesize 160

set
pagesize 9999

alter
session set nls_date_format=’dd-mon-RR hh24:mi:ss’;

col
tbs format a12

col
owner format a8

col
segment format a30

col
type format a12

select
tablespace_name tbs,owner, segment_name segment, segment_type type,

initial_extent
“init B”,next_extent “next B”,extents EXTENTS, pct_increase
pctinc

from
dba_segments

where
tablespace_name in (‘SHARED’,’BISD’,’GMDD’,’GMDX’,’WSHD’,’GLD’)

and
(next_extent/1024/1024)> (select max(bytes/1024/1024) from dba_free_space
where

tablespace_name
in (‘SHARED’,’BISD’,’GMDD’,’GMDX’,’WSHD’,’GLD’))

order
by tablespace_name,segment_name;

select
tablespace_name tbs,owner, segment_name segment, segment_type type,

initial_extent
“init B”,next_extent “next B”,extents EXTENTS, pct_increase
pctinc

from
dba_segments

where
tablespace_name in (‘APPS_TS_SEED’)

and
(next_extent/1024/1024)> (select max(bytes/1024/1024) from dba_free_space
where

tablespace_name
in (‘APPS_TS_SEED’))

order
by tablespace_name,segment_name;