set pages 10000 lines 132 trimspool on feedback off heading on  verify off  echo off
col owner form a15
col segment_name form a40
col tablespace_name form a40
col nxt_mb form 999,999,999.00

select s.owner, s.segment_name, s.tablespace_name, extents, max_extents
from    dba_segments s
where segment_type  not in ('ROLLBACK', 'TEMPORARY')
and   extents > max_extents - 50
/


select s.owner, s.segment_name, s.tablespace_name, nvl(s.next_extent,s.initial_extent)/1024/1024 nxt_mb
from    dba_segments s
where segment_type  not in ('ROLLBACK', 'TEMPORARY')
and not exists (
	select 1
	from dba_free_space f
	where f.tablespace_name = s.tablespace_name
	and   f.bytes > nvl(s.next_extent,s.initial_extent))
order by 1,2,3;

select 'alter ' || segment_type || ' ' ||  s.owner || '.' || s.segment_name || ' storage ( maxextents ' || max_extents*2 || ');'
from    dba_segments s
where segment_type  not in ('ROLLBACK', 'TEMPORARY', 'CACHE')
and   extents > max_extents - 2;


set feedback on heading on verify off echo off