set echo off
set heading off
select 'Instance: '||name||'.  Time: ' || to_char(sysdate, 'DD-Mon-YY HH24:mi:ss')
from   v$database
/

set feedback on heading on
set linesize 100
column owner format a12
column segment_name format a25
column type format a8
column tablespace_name format a15
column next format 999999999
column avail heading "Largest|Free|Extent"
column xcnt heading "Extents|Alloc" format 999999

select  s.owner, s.segment_name,
        s.segment_type type, s.tablespace_name, t.next_extent next,
        s.extents xcnt,
        max(f1.bytes) avail
from    sys.dba_segments        s,
        sys.dba_tables          t,
        sys.dba_free_space      f1
where   s.segment_type          = 'TABLE'
        and s.segment_name      = t.table_name
        and s.owner             = t.owner
        and f1.tablespace_name  = t.tablespace_name
        and ( ( not exists (    select  tablespace_name
                                from    dba_free_space f
                                where   f.tablespace_name = t.tablespace_name
                                and     f.bytes >= t.next_extent))
                or
                s.extents = s.max_extents )
group by s.owner, s.segment_name, s.segment_type, s.tablespace_name,
t.next_extent, s.extents
union
select  s.owner, s.segment_name,
        s.segment_type type, s.tablespace_name, c.next_extent next,
        s.extents xcnt,
        max(f1.bytes) avail
from    sys.dba_segments        s,
        sys.dba_clusters        c,
        sys.dba_free_space      f1
where   s.segment_type          = 'CLUSTER'
        and s.segment_name      = c.cluster_name
        and s.owner             = c.owner
        and f1.tablespace_name  = c.tablespace_name
        and ( ( not exists (    select  tablespace_name
                                from    dba_free_space f
                                where   f.tablespace_name = c.tablespace_name
                                and     f.bytes >= c.next_extent))
                or
                s.extents = s.max_extents )
group by s.owner, s.segment_name, s.segment_type, s.tablespace_name,
c.next_extent, s.extents
union
select  s.owner, s.segment_name,
        s.segment_type type, s.tablespace_name, i.next_extent next,
        s.extents xcnt,
        max(f1.bytes) avail
from    sys.dba_segments        s,
        sys.dba_indexes         i,
        sys.dba_free_space      f1
where   s.segment_type          = 'INDEX'
        and s.segment_name      = i.index_name
        and s.owner             = i.owner
        and s.owner             = i.owner
        and f1.tablespace_name  = i.tablespace_name
        and ( ( not exists (    select  tablespace_name
                                from    dba_free_space f
                                where   f.tablespace_name = i.tablespace_name
                                and     f.bytes >= i.next_extent))
                or
                s.extents = s.max_extents )
group by s.owner, s.segment_name, s.segment_type, s.tablespace_name,
i.next_extent, s.extents
union
select  s.owner, s.segment_name,
        s.segment_type type, s.tablespace_name, r.next_extent next,
        s.extents xcnt,
        max(f1.bytes) avail
from    sys.dba_segments        s,
        sys.dba_rollback_segs   r,
        sys.dba_free_space      f1
where   s.segment_type          = 'ROLLBACK'
        and s.segment_name      = r.segment_name
        and s.owner             = r.owner
        and f1.tablespace_name  = r.tablespace_name
        and ( ( not exists (    select  tablespace_name
                                from    dba_free_space f
                                where   f.tablespace_name = r.tablespace_name
                                and     f.bytes >= r.next_extent))
                or
                s.extents = s.max_extents )
group by s.owner, s.segment_name, s.segment_type, s.tablespace_name,
r.next_extent, s.extents
/