TABLES THAT CANNOT EXTEND

 

Object Extent Warning

TABLES THAT CANNOT EXTEND NOTES:

Owner - Owner of the object

Object Name - Name of the object

Object Type - Type of object

Tablespace - Name of the tablespace

Next Extent - Size of next extent (bytes)

select  OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 TABLESPACE_NAME,
 NEXT_EXTENT
from (
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   t.NEXT_EXTENT
 from  dba_segments seg,
   dba_tables t
 where  (seg.SEGMENT_TYPE = 'TABLE'
 and    seg.SEGMENT_NAME = t.TABLE_NAME
 and    seg.owner = t.OWNER
 and    NOT EXISTS (
   select  TABLESPACE_NAME
    from  dba_free_space free
    where  free.TABLESPACE_NAME = t.TABLESPACE_NAME
    and  BYTES >= t.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   c.NEXT_EXTENT
 from  dba_segments seg,
   dba_clusters c 
 where   (seg.SEGMENT_TYPE = 'CLUSTER'
 and      seg.SEGMENT_NAME = c.CLUSTER_NAME
 and      seg.OWNER = c.OWNER
 and     NOT EXISTS (
   select  TABLESPACE_NAME
   from  dba_free_space free
   where  free.TABLESPACE_NAME = c.TABLESPACE_NAME
   and  BYTES >= c.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   i.NEXT_EXTENT
 from  dba_segments seg,
   dba_indexes  i
 where   (seg.SEGMENT_TYPE = 'INDEX'
 and      seg.SEGMENT_NAME = i.INDEX_NAME
 and      seg.OWNER        = i.OWNER
 and      NOT EXISTS (
   select  TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = i.TABLESPACE_NAME
   and  BYTES >= i.NEXT_EXTENT))
 union
 select  seg.OWNER, 
  seg.SEGMENT_NAME,
   seg.SEGMENT_TYPE, 
  seg.TABLESPACE_NAME,
   r.NEXT_EXTENT
 from  dba_segments seg,
   dba_rollback_segs r
 where   (seg.SEGMENT_TYPE = 'ROLLBACK'
 and      seg.SEGMENT_NAME = r.SEGMENT_NAME
 and      seg.OWNER        = r.OWNER
 and      NOT EXISTS (
   select TABLESPACE_NAME
     from  dba_free_space free
     where  free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and  BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order  by OWNER,SEGMENT_NAME