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