--
-- Maxnextextents.sql
--

set pagesize 66
set echo off
set feedback off
set verify off

ttitle 'Tablespace Max Next Extents Size(Tables)'

spool maxnextext.lst

col col0 heading 'Tablespace_Name' format a15
col col1 heading 'MAX NX_EXT_Byte' format 99,999,999,999
col col2 heading 'MAX Seg_Byte' format 99,999,999,999
col col3 heading 'Check' format 99,999,999,999

select  a.tablespace_name col0,max(a.next_extent) col1,max(b.bytes) col2,
max(b.bytes)-max(a.next_extent) col3
from    dba_tables a,dba_free_space b
where a.tablespace_name=b.tablespace_name
group by a.tablespace_name,b.tablespace_name
union all
select c.tablespace_name col0, max(c.next_extent) col1,max(d.bytes) col2,
max(d.bytes)-max(c.next_extent) col3
from dba_indexes c,dba_free_space d
where c.tablespace_name=d.tablespace_name
group by c.tablespace_name,d.tablespace_name
/
ttitle off
spool off