spool /tmp/extents.lst
prompt
prompt Script Name:	extents.sql
prompt
prompt Author:		P.Noonan ( SGA Consulting )
prompt
prompt Purpose:  	List segments within 70 of max extents
prompt
prompt Output file: 	/tmp/extents.lst (132 Columns )
prompt


col segment_name format a30
col tablespace_name format a15
col owner format a10
set termout off
set linesize 132

col value format a20 head "DB NAME"
col "Date" format a30


select value, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Date"
  from v$parameter where name='db_name';

select segment_name,
       segment_type type,
       owner,
       tablespace_name,
       extents,
       max_extents,
       round(initial_extent / 1048576,2) INIT_MB,
       round(next_extent / 1048576,2) NEXT_MB
from   dba_segments
where max_extents - extents < 70
order by owner, tablespace_name
/

set termout on
spool off
REM !lp /tmp/extents.lst
exit